What is cause of ORA-38104 error on SQL merge?
How about this, the outer join means the rid
will be null and thus fail, and so flow into the WHEN NOT MATCHED
part of the statement if you have one
MERGE INTO target_table tgtUSING ( SELECT t2.ROWID AS rid , s2.c2 FROM target_table t2 , source_table s2 WHERE t2.c1 (+) = s2.c1 ) srcON (tgt.rowid = src.rid)WHEN MATCHED THENUPDATE SET tgt.c1=src.c2
You can exploit some workarounds for ORA-38104, which seem to work up until Oracle 18c, including this one, where you'd wrap your columns in a row value expression that contains an additional dummy expression:
MERGE INTO target_table tgtUSING source_table srcON ((tgt.c1, 'dummy') = ((src.c1, 'dummy')))WHEN MATCHED THENUPDATE SET tgt.c1=src.c2