oracle ORA-01407: 无法更新为空
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15778930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORA-01407:cannot update to null
提问by user2238790
My sql query for updating column "p1_high_edu_ctry" is:-
我更新列“p1_high_edu_ctry”的 sql 查询是：-
update ps_personal_data J
set (J.p1_high_edu_ctry) = (SELECT P.p1_high_edu_ctry FROM PS_PERS_DATA_EFFDT P
WHERE P.EFFDT=(SELECT MAX(K.EFFDT) FROM PS_PERS_DATA_EFFDT K
WHERE K.EFFDT<=SYSDATE
AND J.EMPLID IN(SELECT H.EMPLID FROM PS_AUDIT_PER_EFFDT H
where h.AUDIT_OPRID='CHGH00000063232')))
where exists(select 1 from PS_AUDIT_PER_EFFDT q where q.emplid=j.emplid) ;
but i m getting an error msg while updating:-
但我在更新时收到错误消息：-
ERROR at line 2:
ORA-01407: cannot update ("SYSADM"."PS_PERSONAL_DATA"."P1_HIGH_EDU_CTRY") to NULL
Any suggestions please,why its not working even if i am handling it with where exists???
请提供任何建议，为什么即使我在存在的地方处理它也不起作用？？？
采纳答案by rs.
Try this
尝试这个
MERGE INTO ps_personal_data J
USING
(
SELECT P.p1_high_edu_ctry, p.emplid
FROM PS_PERS_DATA_EFFDT P
WHERE P.EFFDT=
(
SELECT MAX(K.EFFDT) FROM PS_PERS_DATA_EFFDT K
WHERE K.EFFDT<=SYSDATE AND P.EMPLID = K.EMPLID
-- updated this condition ,
-- you need to check max date by emplid
-- not max date from full table
)
AND P.EMPLID IN (SELECT H.EMPLID FROM PS_AUDIT_PER_EFFDT H
where h.AUDIT_OPRID='CHGH00000063232')
) v ON (J.emplid = v.emplid)
WHEN MATCHED THEN UPDATE
set J.p1_high_edu_ctry = nvl(v.p1_high_edu_ctry,' ');
回答by Kirill Leontev
You have a not null constraint on this field while your subquery doesn't return any data, returning null. This is a documented behavior of scalar subquery expression
您在此字段上有一个非空约束，而您的子查询不返回任何数据，返回空值。这是标量子查询表达式的记录行为
12:28:52 SYSTEM@dwal> create table n (t number not null);
Table created.
Elapsed: 00:00:00.31
12:39:18 SYSTEM@dwal> insert into n values (1);
1 row created.
Elapsed: 00:00:00.01
12:40:12 SYSTEM@dwal> update n set t = (select 2 from dual where 1 = 2);
update n set t = (select 2 from dual where 1 = 2)
*
ERROR at line 1:
ORA-01407: cannot update ("SYSTEM"."N"."T") to NULL
Elapsed: 00:00:00.03
