我有一个带有删除标志的简单表(记录应在此列中更新而不是删除):
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 的匹配记录,我希望将主键违规作为例外,这就是为什么我无法将“TARGET.ISDELETED = 1”从 on 子句移动到 update 语句。