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

Contrary to the accepted response, there is actually a way to pull this off: move the offending bit out of the ON clause and into the WHERE clause of the update statement:

merge into ET.PSEUDODELETETABLE TARGETusing (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCEon (SOURCE.ID = TARGET.ID)when matched then  update       set ISDELETED = 0,       NAME = SOURCE.NAME  where TARGET.ISDELETED = 1 -- Magic!when not matched then  insert       values (SOURCE.ID, SOURCE.NAME, 0);

Putting the column in some expression and renaming it seems to work. In the below example, ISDELETED_ and ISDELETED are effectively the same thing:

merge into (  select nvl(ISDELETED, ISDELETED) as ISDELETED_, ISDELETED, ID,   from ET.PSEUDODELETETABLE) TARGETusing (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCEon (TARGET.ISDELETED_ = 1 and SOURCE.ID = TARGET.ID) -- Use the renamed version herewhen matched then  update set ISDELETED = 0, NAME = SOURCE.NAME       -- Use the original version herewhen not matched then  insert values (SOURCE.ID, SOURCE.NAME, 0);

Notice:

• Just renaming doesn't work. The parser seems to be "smart" enough to detect that it's still the same column. But renaming and putting it in a "silly" expression outsmarts the parser.
• This obviously comes at a cost. Indexes may not be usable easily on the renamed column, do check the execution plan. In this particular example, it might work
• Oracle might "fix" this in the future (and make ORA-38104 detection more consistent), so this workaround might break.

This also seems to work, but definitely doesn't seem to allow for any reasonable index usage (do check again on your version of Oracle):

merge into ET.PSEUDODELETETABLE TARGETusing (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCEon ((select TARGET.ISDELETED from dual) = 1 and SOURCE.ID = TARGET.ID)when matched then  update set ISDELETED = 0, NAME = SOURCE.NAMEwhen not matched then  insert values (SOURCE.ID, SOURCE.NAME, 0);

Even this works (which raises serious doubts about the ORA-38104 check as a whole)!

merge into ET.PSEUDODELETETABLE TARGETusing (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCEon ((TARGET.ISDELETED, 'dummy') = ((1, 'dummy')) and SOURCE.ID = TARGET.ID)when matched then  update set ISDELETED = 0, NAME = SOURCE.NAMEwhen not matched then  insert values (SOURCE.ID, SOURCE.NAME, 0);

I have blogged about these workarounds (and execution plans) here.

I suspect you're better off in this case with a shoot-then-look algorithm.

Depending on what you expect to be the more frequent case, either:

• Update, and if no rows are updated, insert; or
• Insert, and if there's a key violation, update.