ORA-01779 processing method (update data processing)

ORA-01779 errors may occur when you try to update a subquery in Oracle. The error message is as follows:

ORA-01779: cannot modify a column which maps to a non-key-preserved table for example, this error occurs when you use the following update queries.

1. Create table test1 (id integer primary key, num integer );
2. Insert into test1 VALUES (1,0);
3. Insert into test1 VALUES (2,0);
4. Insert into test1 VALUES (3,0);
5. Insert into test1 VALUES (4,0);
6. Create table test2 (id integer, num integer, upd integer );
7. Insert into test2 VALUES (1,10,0);
8. Insert into test2 VALUES (2,20,1);
9. UPDATE (SELECT T1.ID ID1, T1.NUM NUM1, T2.ID ID2, T2.NUM NUM2
10. FROM TEST1 T1, TEST2 T2
11. WHERE T1.ID = T2.ID
12. AND T2.UPD =1)
13. SET NUM1 = NUM2;
14. ORA-01779: Cannot modify a column which maps to a non-key-preserved table

This error indicates that the content of the updated data source (test2) is not unique in the subquery results, so that a row in the updated object (test1) may correspond to the data source (test2). In this example, the IDs of table test2 are not unique. Therefore, data with the same id but different num may exist in Table test2. Such data cannot be used to update table test1.

The solution is to ensure the uniqueness of the data source. For example, you can create a unique index for test2.id in this example:

Create unique index test2_idx_001 ON test2 (id );

Then the above update can be executed.

You can also force Oracle execution by adding BYPASS_UJVC annotations.

1. UPDATE (SELECT/* + BYPASS_UJVC */
2. T1.ID ID1, T1.NUM NUM1, T2.ID ID2, T2.NUM NUM2
3. FROM TEST1 T1, TEST2 T2
4. WHERE T1.ID = T2.ID
5. AND T2.UPD =1)
6. SET NUM1 = NUM2;

BYPASS_UJVC skips the Oracle key check. In this way, although it can be executed, if test2 contains not unique data, test1 will be updated multiple times, resulting in unexpected results.