Wsrtjtyk

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?

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

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.

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