Oracle in World: ORA-01779: cannot modify a column which maps to a non key-preserved table

Problem Description
I wanted to update a table column based on another table data but it fails with error
ORA-01779: cannot modify a column which maps to a non key-preserved table.

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;
set col1=col2
*
ERROR at line 4:
ORA-01779: cannot modify a column which maps to a non key-preserved table

Cause, Description and Solution of the Problem
Let's look both of table's data.

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

From the above update query, the select part return following rows,

SQL> select t1.code col1, t2.code col2 from table_1 t1
JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row

In this case col1 value will be replaced by col2 value. Now if source table's id column is not unique then see the below case:

SQL> insert into table_2 values(2,'Test Row');

1 row created.

SQL> select t1.code col1, t2.code col2 from table_1 t1
JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row
Rows to be updated   Test Row

Now the col1 data "Rows to be updated" will be replaced by which value of col2? "Second Row" or "Test Row" which is ambiguous. So in order to update col1 in this way the id of the table containing col2 must be unique so that ambiguous situation will not occur.

SQL> rollback;

Rollback complete.

Adding an unique constraint will solve the problem.
SQL> alter table table_2 add constraint table_2_uk unique(id);

Table altered.

Now we have unique on id. So no ambiguous situation will occur.

SQL> select t1.code col1, t2.code col2 from table_1 t1
JOIN table_2 t2 ON t1.id=t2.id;

COL1                 COL2
-------------------- --------------------
Rows to be updated   Second Row

So update will work fine.

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

5 comments:

QuincyApril 14, 2009 at 1:37 AM
Nice simple-to-understand article to understand the nature of this error. Thanks mate!
pdb9000May 25, 2009 at 3:20 AM
The DEFINITIVE guide on Error ORA-01779.
Thank you very much,

pdb9000

ps. I come from ASE SYBASE...this kind of updates are far more easy in the sybase transact-sql language (update statement may have the "FROM tables" clause)
Nadera Sultana TanyNovember 17, 2009 at 8:32 PM
Thank you very much, You saved my day.
flangeMarch 19, 2010 at 1:31 AM
Nice one, thank you.
SerpicoMarch 25, 2010 at 9:40 AM
Very nice and informative article! Thank you!

Wednesday, January 7, 2009

ORA-01779: cannot modify a column which maps to a non key-preserved table

5 comments: