各位朋友请帮我看看我的SQL语句

Question

UPDATE PS_BU_ITEMS_INV BUSET last_mo_demand =(SELECT  SUM(QTY_SHIPPED)
                   FROM  PS_A8_ALL_INVOICE A8
WHERE  BU.BUSINESS_UNIT = 'AMAX'
AND    BU.INV_ITEM_ID= A8.INV_ITEM_ID
AND    INVOICE_DT>=SYSDATE - 30
GROUP BY A8.INV_ITEM_ID)
但是却一直提示 "(Error): ORA-01407: cannot update ("SYSADM"."PS_BU_ITEMS_INV"."LAST_UD_DEMAND") to NULL"解释下last_ud_demand和QTY_SHIPPED均为数字类型,表PS_BU_ITEMS_INV产品总表,表PS_A8_ALL_INVOICE 为发表总表.需要UPDATE "last_mo_demand" 为最近一个月的销售量.我把上述SUM(QTY_SHIPPED)修改成DECODE(SUM(QTY_SHIPPED),NULL,0,SUM(QTY_SHIPPED))或者SUM(DECODE(QTY_SHIPPED,NULL,0,QTY_SHIPPED)),还是出现那个问题,请各位朋友出出主意. GROUP BY A8.INV_ITEM_ID)

Answer 1

UPDATE PS_BU_ITEMS_INV BU
SET last_mo_demand = (SELECT nvl(SUM(QTY_SHIPPED),0)
                      FROM PS_A8_ALL_INVOICE A8
                      WHERE A8.INV_ITEM_ID = BU.INV_ITEM_ID AND A8.INVOICE_DT> = SYSDATE-30
                      GROUP BY A8.INV_ITEM_ID)
WHERE BU.BUSINESS_UNIT = 'AMAX'

Answer 2

我觉得是不是在update时不能select操作啊？

Answer 3

SET last_mo_demand 
为什么会提示("SYSADM"."PS_BU_ITEMS_INV"."LAST_UD_DEMAND"

Answer 4

呵呵,SORRY,我帖子上写的时候笔误了,提示里也应该是LAST_MO_DEMAND! ORARichard非常细心,谢谢!!
请各位朋友看的时候暂且忽略这个错误吧

Answer 5

UPDATE PS_BU_ITEMS_INV BUSET last_mo_demand =(SELECT  SUM(QTY_SHIPPED)
                   FROM  PS_A8_ALL_INVOICE A8
WHERE  BU.BUSINESS_UNIT = 'AMAX'
AND    BU.INV_ITEM_ID= A8.INV_ITEM_ID
AND    INVOICE_DT>=SYSDATE - 30
GROUP BY A8.INV_ITEM_ID)
WHERE EXISTS
      (
         SELECT 1 FROM PS_A8_ALL_INVOICE A8 
                WHERE BU.BUSINESS_UNIT = 'AMAX' AND 
                      BU.INV_ITEM_ID=A8.INV_ITEM_ID AND 
                      INVOICE_DT>=SYSDATE - 30
      )

Answer 6

同意 libin_ftsafe(子陌红尘) 是因为SELECT未找到记录，值为NULL你的数据库不允许插入NULL！

Answer 7

TO ORARichard(没钱的日子好难过啊) :.......................
.......................
WHERE EXISTS
      (
         SELECT 1 FROM PS_A8_ALL_INVOICE A8 
                WHERE BU.BUSINESS_UNIT = 'AMAX' AND 
                      BU.INV_ITEM_ID=A8.INV_ITEM_ID AND 
                      INVOICE_DT>=SYSDATE - 30
      )中SELECT 1 FROM....能否用SELECT 'X' FROM....替换,俩者有多大区别,谢谢!!

Answer 8

UPDATE PS_BU_ITEMS_INV BU
SET last_mo_demand =(
select SUM(QTY_SHIPPED) from (
select SUM(QTY_SHIPPED),INV_ITEM_ID from PS_A8_ALL_INVOICE group by INV_ITEM_ID) A8
 where BU.BUSINESS_UNIT = 'AMAX'
AND    BU.INV_ITEM_ID= A8.INV_ITEM_ID
AND    INVOICE_DT>=SYSDATE - 30)

Answer 9

没区别，是一样的效果,但仅限用在exists中

Answer 10

谢谢各位回答我,特别是感谢 ORARichard!!
如 ORARichard的答案,问题已经解决.
小弟分少,只能稍微给点了,别嫌弃~~!!