Oracle in World: Updating a table based on another table

In many times we need to update column(s) of a table based on the data of another table column(s). There are several ways to do the task.

Let's show example by creating table and entering values into it.

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

After above statements let's look at the data on the table.

SQL> select * from table_1;

ID CODE
---------- --------------------
1 First Row
2 Rows to be updated

SQL> select * from table_2;

ID CODE
---------- --------------------
2 Second Row

Now my requirement is to update table_1 based on table_2 id column data. If corresponding id in table_1 exist then that row's code will be updated.

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:
In order to apply method 03 you need a primary or unique key column in the source table i.e in the table from where we are fetching data for update. It is needed because if this CONSTRAINT is not there then it will result in multiple rows which will create an ambiguous situation.

So, I am 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

Oracle in World

Saturday, January 3, 2009

Updating a table based on another table

No comments:

Post a Comment