SQL> 
SQL> drop table t1_a;

Table dropped.

SQL> drop table t2_a;

Table dropped.

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> -- OK here
SQL> update t1_a set c2='UU'
  2  where exists
  3    (select * from t2_a where t2_a.c1=t2_a.c1 and t1_a.c1=3);

1 row updated.


Execution Plan
----------------------------------------------------------                      
   0      UPDATE STATEMENT Optimizer=CHOOSE                                     
   1    0   UPDATE OF 'T1_A'                                                    
   2    1     FILTER                                                            
   3    2       TABLE ACCESS (FULL) OF 'T1_A'                                   
   4    2       FILTER                                                          
   5    4         TABLE ACCESS (FULL) OF 'T2_A'                                 



SQL> 
SQL> rollback;

Rollback complete.

SQL> 
SQL> -- Not OK here
SQL> -- ORA-01427: single-row subquery returns more than one row
SQL> update t1_a set c2=(select t2_a.c2 from t2_a where t1_a.c1=t2_a.c1)
  2  where exists
  3    (select * from t2_a where t1_a.c1=t2_a.c1 and t1_a.c1=3);
update t1_a set c2=(select t2_a.c2 from t2_a where t1_a.c1=t2_a.c1)
                    *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row 


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> -- No problem here
SQL> update t1_a set c2=(select t2_a.c2 from t2_a where t1_a.c1=t2_a.c1)
  2  where exists
  3    (select * from t2_a where t1_a.c1=t2_a.c1 and t1_a.c1=4);

2 rows updated.


Execution Plan
----------------------------------------------------------                      
   0      UPDATE STATEMENT Optimizer=CHOOSE                                     
   1    0   UPDATE OF 'T1_A'                                                    
   2    1     FILTER                                                            
   3    2       TABLE ACCESS (FULL) OF 'T1_A'                                   
   4    2       FILTER                                                          
   5    4         TABLE ACCESS (FULL) OF 'T2_A'                                 
   6    1     TABLE ACCESS (FULL) OF 'T2_A'                                     



SQL> 
SQL> rollback;

Rollback complete.

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> 
SQL> set autotrace off
SQL> 
SQL> spool off