CODEWITHSUNDEEP
oracle-databaseoracle9i
suhtite
suhtite

Reputation: 71

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

How can I solve this error:

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

My Code:

UPDATE   (SELECT SOBS034.T1 as OLD, SOBS063.T1 as NEW
          FROM SOBS034 
          INNER JOIN SOBS063  ON SOBS034.ID = SOBS063.ID
          where  SOBS034.ID='111000' AND SOBS063.T2='' ) t
SET t.OLD =t.NEW

Upvotes: 1

Views: 8506

Answers (2)

Srini V
Srini V

Reputation: 11365

Try to create a unique index on SOBS034 (ID) and SOBS063 (ID).

Upvotes: 2

Vincent Malgrat
Vincent Malgrat

Reputation: 67722

To update a JOIN, Oracle needs to be absolutely sure that for each row of the table you are trying to update, there will be at most one row of the joined table.

Here you'll be able to update the join if and only if SOBS063.ID is unique (explicitely declared by a unique constraint/pk).

If somehow SOBS063.ID is unique for this record with your join condition but is not declared as such, you won't be able to use this method. You could however transform this DML into an equivalent MERGE, something like this:

MERGE INTO SOBS034 a
USING SOBS063 b
ON (a.id = b.id AND a.ID='111000' AND b.T2 IS NULL)
WHEN MATCHED THEN UPDATE SET a.t1 = b.t1;

By the way SOBS063.T2='' is never true in Oracle right now.

Upvotes: 3

Related Questions