請問各位,為什麼在UPDATE語句中不能用查詢語句返回的值來更新字段值呢?? 要怎麼辦? 怎麼改?

[laker_tmj]

請問各位,為什麼在UPDATE語句中不能用查詢語句返回的值來更新字段值呢?? 要怎麼辦? 怎麼改?

(我使用的@TOTALA為什麼返不回查詢語句所返回的值呢？？？)

@TOTALA=
(
CASE WHEN DB='MRP' THEN ISNULL((SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) AS TT FROM #TEMP1 A WHERE A.ITEM_CODE=@STRITEM AND DB='MRP' AND IDS<=@IDS ),0)
WHEN DB='MRPM' THEN ISNULL((SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) AS TT FROM #TEMP1 A WHERE A.ITEM_CODE=@STRITEM AND DB='MRPM' AND IDS<=@IDS ),0)
ELSE 0 END
)


使用的語句如下：


DECLARE @IDS INT
DECLARE @INX FLOAT
DECLARE @TOTALA INT
DECLARE @TOTALB INT
DECLARE @STRITEM NVARCHAR(50)
DECLARE @STRCHECKDATE NVARCHAR(50)
SELECT @IDS=0,@INX=0,@TOTALA=0,@TOTALB=0,@STRITEM='',@STRCHECKDATE=''

UPDATE #TEMP1 SET @IDS=IDS,@STRITEM=ITEM_CODE,@STRCHECKDATE=CONVERT(NVARCHAR(10),DATE_EXP,120),
@TOTALA=
(
CASE WHEN DB='MRP' THEN ISNULL((SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) AS TT FROM #TEMP1 A WHERE A.ITEM_CODE=@STRITEM AND DB='MRP' AND IDS<=@IDS ),0)
WHEN DB='MRPM' THEN ISNULL((SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) AS TT FROM #TEMP1 A WHERE A.ITEM_CODE=@STRITEM AND DB='MRPM' AND IDS<=@IDS ),0)
ELSE 0 END
),
TOTALA=ISNULL(@TOTALA,-1),
@TOTALB=
(
CASE WHEN DB='MRP' THEN (SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) FROM #TEMP1 A WHERE A.ITEM_CODE=@STRITEM AND DB='MRPM' AND CONVERT(NVARCHAR(10),DATE_EXP,120)<=@STRCHECKDATE)
WHEN DB='MRPM' THEN (SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) FROM #TEMP1 A WHERE A.ITEM_CODE=@STRITEM AND DB='MRP' AND CONVERT(NVARCHAR(10),DATE_EXP,120)<=@STRCHECKDATE)
ELSE 0 END
),
TOTALB=ISNULL(@TOTALB,-1)
WHERE ITEM_CODE='700270307A0R'

[子陌红尘]

UPDATE #TEMP1
SET
TOTALA=(CASE WHEN DB='MRP'
THEN ISNULL((SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) FROM #TEMP1 WHERE ITEM_CODE=a.ITEM_CODE AND DB='MRP' AND IDS<=a.IDS),0)
WHEN DB='MRPM'
THEN ISNULL((SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) FROM #TEMP1 WHERE ITEM_CODE=a.ITEM_CODE AND DB='MRPM' AND IDS<=a.IDS),0)
ELSE -1
END),
TOTALB=(CASE WHEN DB='MRP'
THEN (SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) FROM #TEMP1 WHERE ITEM_CODE=a.ITEM_CODE AND DB='MRPM' AND CONVERT(NVARCHAR(10),DATE_EXP,120)<=CONVERT(NVARCHAR(10),a.DATE_EXP,120))
WHEN DB='MRPM'
THEN (SELECT MAX(STOCK_QTYA)+SUM(NET_QTY+INX) FROM #TEMP1 WHERE ITEM_CODE=a.ITEM_CODE AND DB='MRP' AND CONVERT(NVARCHAR(10),DATE_EXP,120)<=CONVERT(NVARCHAR(10),a.DATE_EXP,120))
ELSE -1
END),
FROM
#TEMP1 a
WHERE
a.ITEM_CODE='700270307A0R'

[子陌红尘]

既然不需要累加，没必要定义变量。