sql - ORA-38104: ON 子句中引用的列无法更新

Question

我有一个带有删除标志的简单表(记录应在此列中更新而不是删除):

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

插入新记录时，我必须检查是否已经存在与主键匹配但 ISDELETED = 1 的记录。在这种情况下，我必须将 ISDELETED 更改为 0 并更新其他列。因此我使用以下合并语句:

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);

在 Sql-Server 上效果很好，但 Oracle 说:

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

如果存在 IDELETED = 0 的匹配记录，我希望主键违规作为异常(exception)，这就是为什么我不能将“TARGET.ISDELETED = 1”从 on 子句移动到更新语句的原因。

Answer 1

与接受的响应相反，实际上有一种方法可以解决这个问题:将有问题的位移出 ON 子句并进入 update 语句的 WHERE 子句:

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);