martes, 13 de enero de 2009

Updating a table based on another table

Create table table_1(id number, code varchar2(20));
insert into table_1 values(1,'First Row');
insert into table_1 values(2, 'Rows to be updated');
Create table table_2(id number, code varchar2(20));
insert into table_2 values(2,'Second Row');

SQL> select * from table_1;
ID CODE---------- --------------------
1 First Row

SQL> select * from table_2;
ID CODE---------- --------------------
2 Second Row


Method 01:

SQL> update table_1 set code= (select t2.code from table_2 t2 JOIN table_1 t1 ON t1.id=t2.id) where table_1.id in(select id from table_2);
1 row updated.

SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row

Method 02:

SQL> update table_1 t1 set code= (select t2.code from table_2 t2 JOIN table_1 t1 ON t2.id=t1.id) where exists (select t2.code from table_2 t2 where t1.id=t2.id);
1 row updated.
SQL> select * from table_1;
ID CODE
---------- --------------------
1 First Row
2 Second Row


Method 03:

adding an unique constraint in table_2.
SQL> alter table table_2 add constraint table_2_UK UNIQUE (id);
Table altered.

SQL> update (select t1.code col1, t2.code col2 from table_1 t1 JOIN table_2 t2 ON t1.id=t2.id) set col1=col2;1 row updated.

SQL> select * from table_1;
ID CODE}
---------- --------------------
1 First Row
2 Second Row