Vfrdtyky

I have table FOO with some ID values in col N_RA_ID for MANUAL% values in col V_CUST_NUMBER and some matching values in col N_RA_ID for NON-MANUAL values in col V_CUST_NUMBER. Here is sample data -

I want to self join on col N_RA_ID and wherever there is exact match, I want to update N_RA_ID to null WHERE V_CUST_NUMBER LIKE 'MANUAL%'

so the output should look like -

I tried using a merge but got ORA-38104. Can anybody please help?

MERGE INTO FOO X 

USING  (

SELECT T1.V_CUST_NUMBER AS MAN_CUST,T1.N_RA_ID, T2.V_CUST_NUMBER

FROM FOO T1

JOIN FOO T2

ON T1.N_RA_ID = T2.N_RA_ID

AND UPPER(T1.V_CUST_NUMBER) NOT LIKE 'MANUAL%'



) Z

ON (X.N_RA_ID = Z.N_RA_ID)

WHEN MATCHED THEN UPDATE

SET X.N_RA_ID = null

WHERE UPPER(X.V_CUST_NUMBER) LIKE 'MANUAL%'





SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "X"."N_RA_ID"

38104. 00000 -  "Columns referenced in the ON Clause cannot be updated: %s"

*Cause:    LHS of UPDATE SET contains the columns referenced in the ON Clause

*Action:

You can do a simple update with an exists () check:

update foo f1

set n_ra_id = null

where v_cust_number like 'MANUAL%'

and exists (

  select *

  from foo f2

  where f2.n_ra_id = f1.n_ra_id

  and v_cust_number not like 'MANUAL%'

);

The main update filters first on all rows starting with 'MANUAL', and the subquery inside the exists() looks for any row in the table with the same ID, but which does not start with 'MANUAL'. If there is no such non-manual row then that clause is false and the manual row is left untouched; if there is a match then the row is updated.

Demo:

select * from foo;



V_CUST_NU    N_RA_ID

--------- ----------

MANUAL033      17024

MANUAL034     589469

MANUAL035     589470

MANUAL036     589478

BHASAD        589478



update foo f1

set n_ra_id = null

where v_cust_number like 'MANUAL%'

and exists (

  select *

  from foo f2

  where f2.n_ra_id = f1.n_ra_id

  and v_cust_number not like 'MANUAL%'

);



1 row updated.



select * from foo;



V_CUST_NU    N_RA_ID

--------- ----------

MANUAL033      17024

MANUAL034     589469

MANUAL035     589470

MANUAL036           

BHASAD        589478

The usual workaround is to select T2.ROWID as RID in the USING clause, and then instead of matching on N_RA_ID you match ON X.ROWID = Z.RID.

Like so:

MERGE INTO FOO X 

USING  (

SELECT T2.ROWID AS RID,           -- ADD THIS HERE

       T1.V_CUST_NUMBER AS MAN_CUST,T1.N_RA_ID, T2.V_CUST_NUMBER

FROM FOO T1

JOIN FOO T2

ON T1.N_RA_ID = T2.N_RA_ID

AND UPPER(T1.V_CUST_NUMBER) NOT LIKE 'MANUAL%'   

) Z

ON (X.ROWID = Z.RID)              -- CHANGE THE "ON" CLAUSE LIKE THIS

WHEN MATCHED THEN UPDATE

SET X.N_RA_ID = null

WHERE UPPER(X.V_CUST_NUMBER) LIKE 'MANUAL%'

As of Oracle 18c, there exist some workarounds that can be applied to such MERGE statements, which outsmart the parser who checks for ORA-38104 preconditions. For example, you could use row value expressions with an additional dummy column comparison:

MERGE INTO FOO X 

USING  (

  SELECT T1.V_CUST_NUMBER AS MAN_CUST,T1.N_RA_ID, T2.V_CUST_NUMBER

  FROM FOO T1

  JOIN FOO T2

  ON T1.N_RA_ID = T2.N_RA_ID

  AND UPPER(T1.V_CUST_NUMBER) NOT LIKE 'MANUAL%'

) Z

ON ((X.N_RA_ID, 'dummy') = ((Z.N_RA_ID, 'dummy')))

WHEN MATCHED THEN UPDATE

SET X.N_RA_ID = null

WHERE UPPER(X.V_CUST_NUMBER) LIKE 'MANUAL%'