SQL> SQL> drop table t1_a; drop table t1_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> drop table t2_a; drop table t2_a * ERROR at line 1: ORA-00942: table or view does not exist SQL> create table t1_a (c1 number, c2 varchar2(2)); Table created. SQL> insert into t1_a values(2,'xx'); 1 row created. SQL> insert into t1_a values(3,'xx'); 1 row created. SQL> insert into t1_a values(4,'xx'); 1 row created. SQL> insert into t1_a values(4,'xx'); 1 row created. SQL> commit; Commit complete. SQL> create table t2_a (c1 number, c2 varchar2(2)); Table created. SQL> insert into t2_a values(3,'yy'); 1 row created. SQL> insert into t2_a values(3,'pp'); 1 row created. SQL> insert into t2_a values(4,'oo'); 1 row created. SQL> insert into t2_a values(5,'ww'); 1 row created. SQL> commit; Commit complete. SQL> SQL> SQL> set autotrace on explain SQL> SQL> -- When more than 1 row from t2 match 1 row in t1.. SQL> -- ORA-30926: unable to get a stable set of rows in the source tables SQL> merge into t1_a 2 using t2_a 3 on (t1_a.c1=t2_a.c1 and t1_a.c1=3) 4 when matched then 5 update set c2='UU' 6 when not matched then 7 insert values(0,'II'); using t2_a * ERROR at line 2: ORA-30926: unable to get a stable set of rows in the source tables SQL> SQL> -- When 1 row from t2 matches more than 1 row in t1 SQL> -- OK SQL> merge into t1_a 2 using t2_a 3 on (t1_a.c1=t2_a.c1 and t1_a.c1=4) 4 when matched then 5 update set c2='UU' 6 when not matched then 7 insert values(0,'II'); 5 rows merged. Execution Plan ---------------------------------------------------------- 0 MERGE STATEMENT Optimizer=CHOOSE (Cost=13617 Card=551368 Byt es=8821888) 1 0 MERGE OF 'T1_A' 2 1 VIEW 3 2 HASH JOIN (OUTER) (Cost=5 Card=82 Bytes=3198) 4 3 TABLE ACCESS (FULL) OF 'T2_A' (Cost=2 Card=82 Bytes= 1312) 5 3 TABLE ACCESS (FULL) OF 'T1_A' (Cost=2 Card=1 Bytes=2 3) SQL> SQL> rollback; Rollback complete. SQL> set autotrace off SQL> SQL> SQL> -- 2 rows in t1_a meet the condition, updated SQL> -- 3 rows from t2_a did not find any row in t1_a that together meet the condition, inserted. SQL> merge into t1_a 2 using t2_a 3 on (t2_a.c2='yy' and t1_a.c1<4) 4 when matched then 5 update set c2='UU' 6 when not matched then 7 insert values(0,'II'); 5 rows merged. SQL> SQL> select * from t1_a; C1 C2 ---------- -- 2 UU 3 UU 4 xx 4 xx 0 II 0 II 0 II 7 rows selected. SQL> rollback; Rollback complete. SQL> SQL> -- 4 rows in t1_a meet the condition, updated SQL> -- 3 rows from t2_a did not find any row in t1_a that together meet the condition, inserted. SQL> merge into t1_a 2 using t2_a 3 on (t2_a.c2='yy') 4 when matched then 5 update set c2='UU' 6 when not matched then 7 insert values(0,'II'); 7 rows merged. SQL> SQL> select * from t1_a; C1 C2 ---------- -- 2 UU 3 UU 4 UU 4 UU 0 II 0 II 0 II 7 rows selected. SQL> rollback; Rollback complete. SQL> SQL> -- This update should have the same meaning as the update part of the merge SQL> -- in the next step. SQL> update t1_a set c2='UU' where 2 exists (select * from t2_a where t1_a.c1=4); 2 rows updated. SQL> select * from t1_a; C1 C2 ---------- -- 2 xx 3 xx 4 UU 4 UU SQL> rollback; Rollback complete. SQL> SQL> -- How do you explain this one? SQL> merge into t1_a 2 using t2_a 3 on (t1_a.c1=4) 4 when matched then 5 update set c2='UU' 6 when not matched then 7 insert values(0,'II'); using t2_a * ERROR at line 2: ORA-30926: unable to get a stable set of rows in the source tables SQL> SQL> -- ORA-30926, as expected. SQL> -- Can not update 1 row with multiple rows SQL> merge into t1_a 2 using t2_a 3 on (t1_a.c1<4) 4 when matched then 5 update set c2='UU' 6 when not matched then 7 insert values(0,'II'); using t2_a * ERROR at line 2: ORA-30926: unable to get a stable set of rows in the source tables SQL> SQL> -- Some strange merge. SQL> merge into t1_a 2 using dual 3 on (dual.dummy is not null and t1_a.c1<4) 4 when matched then 5 update set c2='UU' 6 when not matched then 7 insert values(0,'II'); 2 rows merged. SQL> SQL> select * from t1_a; C1 C2 ---------- -- 2 UU 3 UU 4 xx 4 xx SQL> SQL> drop table t1_a; Table dropped. SQL> drop table t2_a; Table dropped. SQL> SQL> set autotrace off SQL> SQL> spool off