Pages

Search This Blog

Tuesday, September 20, 2011

[Oracle] Update Data in Table trough Joins

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);

select * from source_table;
select * from target_table;

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
;
select * from source_table;
select * from target_table;

No comments: