第一次执行前一段代码可以更新,然后再执行第二段代码就会报错?
UPDATE SYS_DAYNAGETIVE A
SET A.REASON=(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='DM')
WHERE EXISTS(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='DM')
------------------------------------------------------
UPDATE SYS_DAYNAGETIVE A
SET A.REASON=(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='SURR')
WHERE EXISTS(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='SURR')
------------------------------------------------------
UPDATE SYS_DAYNAGETIVE A
SET A.REASON=(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='DM')
WHERE EXISTS(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='DM')
------------------------------------------------------
UPDATE SYS_DAYNAGETIVE A
SET A.REASON=(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='SURR')
WHERE EXISTS(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='SURR')
------------------------------------------------------
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='SURR'因为这段查出了多个值
SET A.REASON = (SELECT B.TYPE
FROM SYS_DAYCONTROL B
WHERE A.STORECODE = B.STORECODE
AND A.ITEMCODE = B.ITEMCODE
AND B.TYPE = 'SURR')
WHERE EXISTS (SELECT B.TYPE
FROM SYS_DAYCONTROL B
WHERE A.STORECODE = B.STORECODE
AND A.ITEMCODE = B.ITEMCODE
AND B.TYPE = 'SURR')
UPDATE SYS_DAYNAGETIVE A
SET A.REASON = (SELECT distinct(B.TYPE)------------这里返回的值不唯一
FROM SYS_DAYCONTROL B
WHERE A.STORECODE = B.STORECODE
AND A.ITEMCODE = B.ITEMCODE
AND B.TYPE = 'SURR')
WHERE EXISTS (SELECT B.TYPE
FROM SYS_DAYCONTROL B
WHERE A.STORECODE = B.STORECODE
AND A.ITEMCODE = B.ITEMCODE
AND B.TYPE = 'SURR')
比如
STORECODE ITEMCODE TYPE
123 4567 SURR
123 4567 SURR
154 6543 SURR
distinct //去掉查询中重复的记录
SET A.REASON=(SELECT B.TYPE FROM SYS_DAYCONTROL B
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='DM')-- 上面有:A.REASON=
-- 如果有“=”(等于)号的话,后面的子查询应该只能返回一行记录,
-- 你想一下:A.REASON=,不能既等于“王五”,又等于“赵六”,对吧?-- 那么这个时候,你得想办法去处理撒,
-- 怎么处理呢?很简单的方法是,限制子查询只返回一行记录,怎么限制呢?......
改成
SELECT B.TYPE FROM SYS_DAYCONTROL B
where rownum<2
WHERE A.STORECODE=B.STORECODE AND A.ITEMCODE=B.ITEMCODE AND B.TYPE='SURR'出来是多行记录啊 悲催啊 这么明显