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?

Answer 1

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

Answer 2

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%');