While updating a column using merge I am getting Error: ORA-38104

user_7677282 0
Viewed 2106

I am trying to update BUNDLE_NAME from below table, My Query is : 

MERGE INTO SOURCE S USING 
(SELECT 'MANIFEST_ID' KEY,'CART' BUNDLE_NAME FROM DUAL) D
ON(S.KEY = D.KEY AND S.BUNDLE_NAME = D.BUNDLE_NAME)
WHEN MATCHED THEN UPDATE
SET BUNDLE_NAME = 'EPI';

but it gives me the following error:

Error report - SQL Error: ORA-38104: Columns referenced in the ON Clause cannot be updated: "S"."BUNDLE_NAME" 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**

--My requirement is that I want to update using merge statement only--

merge oracle
2 Answers

You can't have bundle_id in the on clause if you're trying to update it. As it's a fixed value, you can have it in a where clause instead:

MERGE INTO SOURCE S
USING (SELECT 'MANIFEST_ID' KEY FROM DUAL) D
ON (S.KEY = D.KEY)
WHEN MATCHED THEN UPDATE
SET BUNDLE_NAME = 'EPI'
WHERE S.BUNDLE_NAME = 'CART';

But you don't need a merge, you can do a simple update:

UPDATE SOURCE S
SET S.BUNDLE_NAME = 'EPI'
WHERE S.KEY = 'MANIFEST_ID'
AND S.BUNDLE_NAME = 'CART';
user_2663040

There are a few possible workarounds which can be used to outsmart the parser, at least until Oracle 18c. One of them is to wrap your predicate in a row value expression predicate using an additional dummy column:

MERGE INTO SOURCE S USING 
(SELECT 'MANIFEST_ID' KEY,'CART' BUNDLE_NAME FROM DUAL) D
ON(S.KEY = D.KEY AND (S.BUNDLE_NAME, 'dummy') = ((D.BUNDLE_NAME, 'dummy')))
WHEN MATCHED THEN UPDATE
SET BUNDLE_NAME = 'EPI';
user_5217990
Related