因此,我有一个更新,我正尝试将其更改为插入
以下是原始的Update语句(在大容量上需要很长的时间)
DECLARE
BEGIN
UPDATE TOCTASK_ORIG TASKO
SET c_ocactvty_childactiviti = 11200,
i_ocactvty_childactiviti =
(SELECT PROI.I
FROM TOCPROCESSINSTANCE PROI,
TOCSERVICEREQUEST SR
WHERE TASKO.c_occontbs_tasks = 11011
AND TASKO.i_occontbs_tasks = SR.I
AND PROI.C_PROCESSIServiceRe = SR.C
AND PROI.I_PROCESSIServiceRe = SR.I
)
WHERE EXISTS
(SELECT 1
FROM TOCPROCESSINSTANCE PROI,
TOCSERVICEREQUEST SR
WHERE TASK.c_occontbs_tasks = 11011
AND TASK.i_occontbs_tasks = SR.I
AND PROI.C_PROCESSIServiceRe = SR.C
AND PROI.I_PROCESSIServiceRe = SR.I
);
COMMIT;
END;根据在线研究,最好的方法是将其重写为insert,并在此过程中进行更改
我在这里所做的就是插入的SELECT子句
SELECT
TASKO.C
,TASKO.I
,11200
,PROI.I
FROM TOCTASK_ORIG TASKO,
TOCPROCESSINSTANCE PROI,
TOCSERVICEREQUEST SR
WHERE TASKO.c_occontbs_tasks = 11011
AND TASKO.i_occontbs_tasks = SR.I
AND PROI.C_PROCESSIServiceRe = 11011
AND PROI.I_PROCESSIServiceRe = SR.I;问题是我当前在原始表中有110302条记录,但是当我在一个新的临时表中执行插入操作时,它只创建了19,269条记录。这个数字与WHERE子句的第二部分匹配,在WHERE子句中,19,269现在应该与PROI.I值匹配。其余的应该为空
AND PROI.C_PROCESSIServiceRe = 11011
AND PROI.I_PROCESSIServiceRe = SR.I问题是没有创建其他记录,它只是创建了19,269条记录并停止
任何帮助都是最好的
发布于 2017-06-27 21:43:17
我能够根据上面提供的一些信息来解决这个问题
FROM TOCTASK_ORIG TASKO
LEFT OUTER JOIN TOCSERVICEREQUEST SR
ON TASKO.c_occontbs_tasks = SR.C
AND TASKO.i_occontbs_tasks = SR.I
LEFT OUTER JOIN TOCPROCESSINSTANCE PROI
ON PROI.C_PROCESSIServiceRe = SR.C
AND PROI.I_PROCESSIServiceRe = SR.I;发布于 2017-06-27 20:18:17
如果您已经观察到了,那么您当前正在执行INNER JOIN,因此只出现匹配的行。而您实际上需要执行像这样的LEFT OUTER JOIN
FROM TOCTASK_ORIG TASKO
LEFT JOIN TOCSERVICEREQUEST SR ON TASKO.i_occontbs_tasks = SR.I
LEFT JOIN TOCPROCESSINSTANCE PROI ON PROI.I_PROCESSIServiceRe = SR.I
AND PROI.C_PROCESSIServiceRe = 11011
WHERE TASKO.c_occontbs_tasks = 11011;https://stackoverflow.com/questions/44780034
复制相似问题