ORA-38104:无法更新ON子句中引用的列
ORA-38104:无法更新ON子句中引用的列
提问于 2011-05-06 00:11:16
我有一个带有删除标志的简单表(应该更新此列中的记录，而不是删除)：
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语句：
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-语句。
回答 4
Stack Overflow用户
发布于 2011-11-29 06:24:23
与接受的响应相反，实际上有一种方法可以做到这一点:将有问题的部分移出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);Stack Overflow用户
发布于 2016-03-05 09:08:36
我们还需要考虑下面的场景，
如果有一个与IDELETED = 0匹配的记录，我希望主键冲突是一个异常，这就是为什么我不能将"TARGET.ISDELETED = 1“从on-子句移动到update-语句。
所以确切的解决方案如下所示，
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;Stack Overflow用户
发布于 2019-01-02 23:14:56
这不就行了吗？
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);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/5900912
复制相关文章
相似问题