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

Question

I have a following SELECT query -

SELECT C.CASE_TITL_NM,RA.V_CUST_NUMBER
FROM KDD_CASES C
JOIN FCT_RA RA
ON RA.N_RA_ID = C.RA_ID
WHERE UPPER(C.CNTRY_KEY_ID) LIKE '%MANUAL%' 
AND C.SCORE_CT IN (99,100)
AND C.STATUS_CD = 'CCD'
AND C.CASE_TITL_NM NOT LIKE 'MANUAL%'

I need to update value in col V_CUST_NUMBER with value in col CASE_TITL_NM so I plugged my SELECT inside following UPDATEstatement and ran it only to get ORA01779 -

    UPDATE (
    SELECT C.CASE_TITL_NM,RA.V_CUST_NUMBER
    FROM KDD_CASES C
    JOIN FCT_RA RA
    ON RA.N_RA_ID = C.RA_ID
    WHERE UPPER(C.CNTRY_KEY_ID) LIKE '%MANUAL%' 
    AND C.SCORE_CT IN (99,100)
    AND C.STATUS_CD = 'CCD'
    AND C.CASE_TITL_NM NOT LIKE 'MANUAL%'
    ) X
    SET X.V_CUST_NUMBER = X.CASE_TITL_NM;

SQL Error: ORA-01779: cannot modify a column which maps to a non key-preserved table
01779. 00000 -  "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.

Can anybody explain what does this error mean and what would be the right UPDATE query?

score 2 · Answer 1 · answered Nov 20 '18 at 23:11

What it means is the query as specified results in an output set that has duplicated rows for RA. Seeing as one RA row maps to two different C rows you cannnot update RA, because there is the potential to try and update the sole RA row to have two different values

You can try using a MERGE statement, using SQL-that-writes-SQL to create a bunch of UPDATE statements, or modifying the join condition so duplicate rows from RA are not present in the output and the primary key from RA is covered

score 2 · Accepted Answer · answered Nov 20 '18 at 23:55

I was able to run my query it by using EXIST clause

UPDATE FCT_RA F
SET F.V_CUST_NUMBER = ( SELECT CASE_TITL_NM 
             FROM KDD_CASES C
             WHERE F.N_RA_ID = C.RA_ID
             AND UPPER(CNTRY_KEY_ID) LIKE '%MANUAL%' 
             AND SCORE_CT IN (99,100)
             AND STATUS_CD = 'CCD'
             AND CASE_TITL_NM NOT LIKE 'MANUAL%')
WHERE EXISTS ( SELECT 1 
             FROM KDD_CASES C
             WHERE F.N_RA_ID = C.RA_ID
             AND UPPER(CNTRY_KEY_ID) LIKE '%MANUAL%' 
             AND SCORE_CT IN (99,100)
             AND STATUS_CD = 'CCD'
             AND CASE_TITL_NM NOT LIKE 'MANUAL%');

score 0 · Answer 3 · answered Nov 20 '18 at 23:19

Please look here, especially about key preserved

The updatable view query must unambiguously return each row of the modified table only one time. The query must be “key preserved”, which means Oracle must be able to use a primary key or unique constraint to ensure that each row is only modified once.

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

3 Answers3