Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01779: cannot modify a column which maps to a non key-preserved table (Oracle 10g)
ORA-01779: cannot modify a column which maps to a non key-preserved table [message #394772] Mon, 30 March 2009 04:51 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Why below query gives an error?
I tested same quest for other tables also.

UPDATE
( SELECT src.VALUE t1, src.ERROR t2, src.SYSCREATEDBY t3,
         tgt.VALUE r1, tgt.ERROR r2, tgt.SYSCREATEDBY r3
    FROM test1 src , test2 tgt
      WHERE src.db_id = tgt.db_id 
	  AND src.FF_ID = tgt.FF_ID
	  AND src.LEAD_ID = tgt.LEAD_ID  
	  AND tgt.lead_id = 237482902 
)
SET t1=r1,
    t2=r2,
	t3=r3

Error on line 0
UPDATE
( SELECT src.VALUE t1, src.ERROR t2, src.SYSCREATEDBY t3,
         tgt.

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


[Updated on: Mon, 30 March 2009 04:56]

Report message to a moderator

Re: ORA-01779: cannot modify a column which maps to a non key-preserved table [message #394775 is a reply to message #394772] Mon, 30 March 2009 05:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You need to have referential integrity constraints in place to guarantee that each value of T1, T2 and T3 will occurr a maximum of once in the list of values returned by the inline View.

If you don't have these constraints, it's much easier to use MERGE.
Your update will still fail if this condition isn't met, but Merge raises the error when it actually happens, rather than when it is possible for it to happen.
Re: ORA-01779: cannot modify a column which maps to a non key-preserved table [message #394777 is a reply to message #394775] Mon, 30 March 2009 05:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
This test will show you the difference. Just run these comands in SQL*PLus:
create table test_164 (col_1  number,  col_2  number);
create table test_165 (col_1  number,  col_3  number);

insert into test_164 values (1,10);
insert into test_164 values (2,10);
insert into test_164 values (3,10);

insert into test_165 values (1,20);
insert into test_165 values (1,30);
insert into test_165 values (3,50);

update (select t1.col_1, t1.col_2, t2.col_3
        from   test_164 t1
              ,test_165 t2
        where  t1.col_1 = t2.col_1)
set col_2 = col_3;

merge into test_164 t1
using test_165 t2
on (t1.col_1 = t2.col_1)
when matched then update set t1.col_2 = t2.col_3;

delete test_165 where col_1 = 1 and col_3 = 30;
commit;

update (select t1.col_1, t1.col_2, t2.col_3
        from   test_164 t1
              ,test_165 t2
        where  t1.col_1 = t2.col_1)
set col_2 = col_3;

merge into test_164 t1
using (select * from test_165) t2
on (t1.col_1 = t2.col_1)
when matched then update set t1.col_2 = t2.col_3;

select * from test_164;
Re: ORA-01779: cannot modify a column which maps to a non key-preserved table [message #395110 is a reply to message #394772] Tue, 31 March 2009 05:50 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks for reply.

So do you mean i need to delete duplicate rows from the table based on the perticular column right?

And without this is that possible to update?
Re: ORA-01779: cannot modify a column which maps to a non key-preserved table [message #395126 is a reply to message #395110] Tue, 31 March 2009 07:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Deleting rows and adding constraints will be neccessary if you want to use UPDATE.

If you want to use MERGE, all you need to do is to ensure that for every value in TEST_164 (in my example), your USING clause returns at most one row.
So, with the original data, you can use MERGE like this:
merge into test_164 t1
using (select col_1,max(col_3) col_3 from test_165 group by col_1) t2
on (t1.col_1 = t2.col_1)
when matched then update set t1.col_2 = t2.col_3;
Re: ORA-01779: cannot modify a column which maps to a non key-preserved table [message #395135 is a reply to message #394772] Tue, 31 March 2009 07:56 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Just to say it in another words: you have a logical problem here.
You UPDATE rows in SRC based on corresponding rows in TGT. (This looks strange, as usual convention is inverse - rows in target table are updated based on rows in source table. Did you not switch the columns in update set clause by mistake?)

When using this kind of update, there shall be only one row in the destination table which corresponds to the updated row.
For UPDATE command, you have ensure this by primary key or unique constraint.
For MERGE command, you do not need this; but as soon as more rows are found for one updated row, the command fails.

When you look at JRowbottom's example, there are initially two rows in TEST_165 with COL_1 = 1 with values 20 and 30 in COL_3. Which value shall be taken to update row in TEST_164 with COL_1 = 1?

Of course it is more convenient to use primary key or unique constraint to ensure data consistency on database level.
Previous Topic: Dynamically prepare the query
Next Topic: To Load a single table at a time from 4 different tables (merged 2)
Goto Forum:
  


Current Time: Wed Dec 07 02:56:19 CST 2016

Total time taken to generate the page: 0.11753 seconds