SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated

Question

MERGE /*+ GATHER_PLAN_STATISTICS*/
     INTO ATM_REQUEST ATM
    USING ATM_STATUS_VIEW ST
       ON (    ATM.accountno = ST.accountno
           AND atm.status IS NULL
           AND atm.remarks IS NULL)
WHEN MATCHED
THEN
   UPDATE SET ATM.STATUS = ST.STATUS, ATM.REMARKS = ST.REMARKS;

am getting below error, can anyone help me on this.

Error at Command Line : 3 Column : 35
Error report -
SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "ATM"."STATUS"

Answer 1

You're using column atm.status in the ON clause, so it can't be changed the the UPDATE clause. You can move the IS NULL conditions into the UPDATE clause.

MERGE /*+ GATHER_PLAN_STATISTICS*/
     INTO atm_request atm
    USING atm_status_view st
       ON (atm.accountno = st.accountno)
WHEN MATCHED
THEN
   UPDATE SET atm.status = st.status, atm.remarks = st.remarks
          WHERE     atm.status IS NULL
                AND atm.remarks IS NULL;