SQL ORA-38104：无法更新 ON 子句中引用的列

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

提问by FreeAndNil

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:

插入新记录时，我必须检查是否已经存在与主键匹配但 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);

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

在 Sql-Server 上它工作得很好，但 Oracle 说：

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.

如果有 IDELETED = 0 的匹配记录，我希望主键违规作为例外，这就是为什么我不能将“TARGET.ISDELETED = 1”从 on-clause 移动到 update-statement。

采纳答案by Adam Musch

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.

• 更新，如果没有行更新，则插入；或者
• 插入，如果有密钥冲突，更新。

回答by David Marx

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:

与接受的响应相反，实际上有一种方法可以解决这个问题：将有问题的位从 ON 子句移到更新语句的 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);

回答by Lukas Eder

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

将列放在某个表达式中并重命名它似乎有效。在下面的例子中，ISDELETED_和ISDELETED实际上是一样的：

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.

• 只是重命名不起作用。解析器似乎足够“聪明”，可以检测到它仍然是同一列。但是重命名并把它放在一个“愚蠢的”表达式中比解析器更聪明。
• 这显然是有代价的。索引在重命名的列上可能不容易使用，请检查执行计划。在这个特定的例子中，它可能会起作用
• Oracle 将来可能会“修复”此问题（并使 ORA-38104 检测更加一致），因此此解决方法可能会中断。

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

这似乎也有效，但似乎绝对不允许任何合理的索引使用（请再次检查您的 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)!

甚至这也有效（这引起了对 ORA-38104 检查整体的严重怀疑）！

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.

我已经在博客中介绍了这些变通方法（和执行计划）。

回答by Toon Koppelaars

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

回答by Mani Kandan

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.

如果有与 匹配的记录IDELETED = 0，我希望主键违规作为例外，这就是为什么我不能将“TARGET.ISDELETED = 1”从 on-clause 移动到 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;

回答by A Costa

"Conditional inserts and updates are now possible by using a WHERE clause on these statements." http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php

“现在可以通过在这些语句上使用 WHERE 子句来进行条件插入和更新。” http://www.oracle-base.com/articles/10g/merge-enhancements-10g.php