ORA-38104 when trying to update my table using merge

Question

I have a stored procedure in which I want to update some columns, so I wrote below code:

PROCEDURE UPDATE_MST_INFO_BKC (
    P_SAPID IN   NVARCHAR2
) AS
BEGIN
    MERGE INTO tbl_ipcolo_billing_mst I 
    USING (
            SELECT
                R4G_STATE,                        -- poilitical state name
                R4G_STATECODE,                    -- poilitical state code
                CIRCLE,                           -- city name                                                            
                NE_ID,
                LATITUDE,
                LONGITUDE,
                SAP_ID

            FROM
                R4G_OSP.ENODEB
            WHERE
                SAP_ID = P_SAPID
                AND ROWNUM = 1
            )
    O ON ( I.SAP_ID = O.SAP_ID )
    WHEN MATCHED THEN 
    UPDATE SET I.POLITICAL_STATE_NAME = O.R4G_STATE,
               I.POLITICAL_STATE_CODE = O.R4G_STATECODE,
                I.CITY_NAME = O.CIRCLE,
                I.NEID = O.NE_ID,
                I.FACILITY_LATITUDE = O.LATITUDE,
                I.FACILITY_LONGITUDE = O.LONGITUDE,
                I.SAP_ID = O.SAP_ID;               

END UPDATE_MST_INFO_BKC;

But it is giving me error as

ORA-38104: Columns referenced in the ON Clause cannot be updated: "I"."SAP_ID"

What am I doing wrong?

Answer 1

You are joining the source to destination tables on I.SAP_ID = O.SAP_ID and then, when matched, are trying to update them and set I.SAP_ID = O.SAP_ID. You cannot update the columns used in the join ... and why would you want to as you have already determined that the values are equal.

Just remove the last line of the UPDATE:

...
O ON ( I.SAP_ID = O.SAP_ID )
WHEN MATCHED THEN 
UPDATE SET I.POLITICAL_STATE_NAME = O.R4G_STATE,
           I.POLITICAL_STATE_CODE = O.R4G_STATECODE,
           I.CITY_NAME = O.CIRCLE,
           I.NEID = O.NE_ID,
           I.FACILITY_LATITUDE = O.LATITUDE,
           I.FACILITY_LONGITUDE = O.LONGITUDE;