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

Question

i have a simple table with a delete-flag (records should be updated in this column instead of deleted):

create table PSEUDODELETETABLE
(
  ID        NUMBER(8) not null, -- PKEY
  NAME      VARCHAR2(50) not null,
  ISDELETED NUMBER(1) default 0 not null
)

When inserting new records I must check, whether there is already a record matching the primary key but having ISDELETED = 1. In that case I must change ISDELETED to 0 and update the other columns. Therefore I'm using the following Merge-Statement:

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

On Sql-Server it works great, but Oracle says:

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

If there is a matching record with IDELETED = 0, I want the primary key violation as an exception, that's why I can't move "TARGET.ISDELETED = 1" from the on-clause to the update-statement.

Answer 1

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 TARGET
using (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCE
on (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);

Answer 2

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
) TARGET
using (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCE
on (TARGET.ISDELETED_ = 1 and SOURCE.ID = TARGET.ID) -- Use the renamed version here
when matched then
  update set ISDELETED = 0, NAME = SOURCE.NAME       -- Use the original version here
when 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 TARGET
using (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCE
on ((select TARGET.ISDELETED from dual) = 1 and SOURCE.ID = TARGET.ID)
when matched then
  update set ISDELETED = 0, NAME = SOURCE.NAME
when 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 TARGET
using (select 1 as ID, 'Horst' as NAME from sys.dual) SOURCE
on ((TARGET.ISDELETED, 'dummy') = ((1, 'dummy')) and SOURCE.ID = TARGET.ID)
when matched then
  update set ISDELETED = 0, NAME = SOURCE.NAME
when not matched then
  insert values (SOURCE.ID, SOURCE.NAME, 0);

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

Answer 3

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.

Answer 4

We need to consider below scenario as well,

If there is a matching record with IDELETED = 0, I want the primary key violation as an exception, that's why I can't move "TARGET.ISDELETED = 1" from the on-clause to the update-statement.

So the exact solution is as below,

begin 
    update ET.PSEUDODELETETABLE set ISDELETED = 0, NAME = 'Horst' 
    where ISDELETED = 1 and ID = 1; 
    if (sql%rowcount = 0) then 
        insert into ET.PSEUDODELETETABLE values (1, 'Horst', 0); 
    end if; 
end;

Answer 5

Won't this just work?

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