Updating from Two Tables

We can update data from one table using data from another table. The following set of statements explains how it is done. One requirement fro updates like this is that both the tables should be key preserved.

SQL> create table tab1 ( col1 number , col2 varchar(20), constraint pk_col1 primary key (col1) );

Table created.

SQL> insert into tab1 values (1 , 'nirmala');

1 row created.

SQL> insert into tab1 values (2 , 'rahul');

1 row created.

SQL> insert into tab1 values (3 , 'rahul');

1 row created.

SQL> insert into tab1 values (4 , 'kumar');

1 row created.

SQL> insert into tab1 values (5 , 'pragati');

1 row created.

SQL> commit;

Commit complete.

SQL> create table tab2 as select * from tab1;

Table created.

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 rahul
3 rahul
4 kumar
5 pragati

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 rahul
3 rahul
4 kumar
5 pragati

SQL> update tab2 set col2 = 'chaitu' where col1 = 2;

1 row updated.

SQL> select * from tab2;
cls COL1 COL2
---------- --------------------
1 nirmala
2 chaitu
3 rahul
4 kumar
5 pragati

SQL> update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source;
update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source
*
ERROR at line 1:
ORA-01779: cannot modify a column which maps to a non key-preserved table


SQL> create unique index idx_tab2 on tab2 (col1);

Index created.

SQL> update (select a.col2 target, b.col2 source from tab1 a, tab2 b where a.col1 = b.col1) set target = source;

5 rows updated.

SQL> commit;

Commit complete.

SQL> select * from tab1;
cls COL1 COL2
---------- --------------------
1 nirmala
2 chaitu
3 rahul
4 kumar
5 pragati