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?

hi, the error is pretty clear .. you cannot update your joining column/columns, so if you delete the last row of the merge statement, the update will work. — F.Lazarescu, Sep 12 '19 at 08:05

score 4 · Accepted Answer · answered Sep 12 '19 at 08:08

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;

score 3 · Answer 2 · answered Sep 12 '19 at 08:05

The error message tells you what the problem is - a MERGE statement cannot update the columns used in the ON clause - and even tells you what column is the problem: "I"."SAP_ID".

So Oracle hurls ORA-38104 because of this line in your WHEN MATCHED branch

I.SAP_ID = O.SAP_ID;

Remove it and your problem disappears. Fortunately the line is unnecessary: I.SAP_ID already equals O.SAP_ID, otherwise the record wouldn't go down the MATCHED branch.

The reason why is quite straightforward: transactional consistency. The MERGE statement operates over a set of records defined by the USING clause and the ON clause. Updating the columns used in the ON clause threatens the integrity of that set, and so Oracle forbids it.

ORA-38104 when trying to update my table using merge

2 Answers2