What is cause of ORA-38104 error on SQL merge?

Question

I have a code like this

MERGE INTO target_table tgt
USING source_table src
on(tgt.c1=src.c1)
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2

I get ORA-38104: Columns referenced in the ON clause cannot be updated. I understand the reason for this error. But how can we rewrite this code? Is there any possibilities without using cursor?

Answer 1

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 tgt
USING ( SELECT t2.ROWID AS rid
            ,  s2.c2
        FROM   target_table t2
             , source_table s2
        WHERE t2.c1 (+) = s2.c1
      ) src
ON (tgt.rowid = src.rid)
WHEN MATCHED THEN
UPDATE SET tgt.c1=src.c2