請問各位,為什麼在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'
