update irpt_departments a set a.parent=(select id from irpt_departments b where a.dept_adrresscode=b.dept_adrresscode and b.isjc='9')
where substr(a.id,1,2)='00';
这句执行后提示:
ERROR 位于第 1 行:
ORA-01407: 无法更新 ("WSZBIRPT"."IRPT_DEPARTMENTS"."PARENT") 为 NULL
怎么才能匹配到的更新,不匹配的不更新呢?谢谢
where substr(a.id,1,2)='00';
这句执行后提示:
ERROR 位于第 1 行:
ORA-01407: 无法更新 ("WSZBIRPT"."IRPT_DEPARTMENTS"."PARENT") 为 NULL
怎么才能匹配到的更新,不匹配的不更新呢?谢谢
SET A.PARENT = (SELECT ID
FROM IRPT_DEPARTMENTS B
WHERE A.DEPT_ADRRESSCODE = B.DEPT_ADRRESSCODE
AND B.ISJC = '9')
WHERE SUBSTR(A.ID, 1, 2) = '00'
AND EXISTS (SELECT 1
FROM IRPT_DEPARTMENTS B
WHERE A.DEPT_ADRRESSCODE = B.DEPT_ADRRESSCODE
AND B.ISJC = '9'
AND ID IS NOT NULL);
update irpt_departments a
set a.parent = (select id
from irpt_departments b
where a.dept_adrresscode = b.dept_adrresscode
and b.isjc = '9')
where substr(a.id, 1, 2) = '00'
and a.dept_adrresscode in
(select dept_adrresscode from irpt_departments);
where exist(select 1 from irpt_departments b where a.dept_adrresscode=b.dept_adrresscode and b.isjc='9');
SET A.PARENT = NVL((SELECT ID
FROM IRPT_DEPARTMENTS B
WHERE A.DEPT_ADRRESSCODE = B.DEPT_ADRRESSCODE
AND B.ISJC = '9'),
A.PARENT)
WHERE SUBSTR(A.ID, 1, 2) = '00';
UPDATE IRPT_DEPARTMENTS A
SET A.PARENT =
case
when NVL((SELECT ID
FROM IRPT_DEPARTMENTS B
WHERE A.DEPT_ADRRESSCODE = B.DEPT_ADRRESSCODE
AND B.ISJC = '9'),
A.PARENT) = null
then 0
when NVL((SELECT ID
FROM IRPT_DEPARTMENTS B
WHERE A.DEPT_ADRRESSCODE = B.DEPT_ADRRESSCODE
AND B.ISJC = '9'),
A.PARENT) != null
then NVL((SELECT ID
FROM IRPT_DEPARTMENTS B
WHERE A.DEPT_ADRRESSCODE = B.DEPT_ADRRESSCODE
AND B.ISJC = '9'),
A.PARENT)
end
WHERE SUBSTR(A.ID, 1, 2) = '00';