oracle update with,Update query with Joins

Oracle has the ability to update a table used in a join, however there is a restriction that Oracle must know in advance that the table acting as the source must be unique for each row in the target. (I hope I worded that correctly). You have the same restriction logically with a subquery because if it returns more than one row the update statement will fail. Here's an example:

create table source_table

(source_id number primary key

,s_col1 number

,s_col2 number

,s_col3 number

);

create table target_table

(target_id number primary key

,t_col1 number

,t_col2 number

,t_col3 number

);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (101,111,1.01,-1);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (102,222,2.02,-2);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (103,333,3.03,-3);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (104,444,4.04,-4);

insert into source_table(source_id,s_col1,s_col2,s_col3) values (105,555,5.05,-5);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (101,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (102,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (103,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (104,null,null,null);

insert into target_table(target_id,t_col1,t_col2,t_col3) values (105,null,null,null);

commit;

SQL> select * from target_table;

TARGET_ID T_COL1 T_COL2 T_COL3

-------------------- -------------------- -------------------- --------------------

101

102

103

104

105

update

(

select t_col1,t_col2,t_col3,s_col1,s_col2,s_col3

from target_table t

,source_table s

where t.target_id = s.source_id

)

set t_col1 = s_col1

,t_col2 = s_col2

,t_col3 = s_col3

;

5 rows updated.

SQL> select * from target_table;

TARGET_ID T_COL1 T_COL2 T_COL3

-------------------- -------------------- -------------------- --------------------

101 111 1.01 -1

102 222 2.02 -2

103 333 3.03 -3

104 444 4.04 -4

105 555 5.05 -5