ORA-01779 processing method (update data processing)

Source: Internet
Author: User

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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.