I am attempting to update a table. Below is my code:
UPDATE tbl_inventory
SET amt = (SELECT SUM(CostAmt) FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1),
gItmAmount = (SELECT SUM(gItmAmount)
FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1)
WHERE tbl_inventory.groceryItm = 1ab0
AND InventoryDtl.groceryitmkey = 1
In some cases, the gItmAmount is null and the table will not accept a null.
I have tried a case statement but I always get an error. "Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'case'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'end'."
Here is my attempt to use the case statement:
UPDATE tbl_inventory
SET amt = (SELECT SUM(CostAmt) FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1),
CASE when gItmAmount is NULL then gItmAmount = '0' else gItmAmount = (SELECT SUM(gItmAmount) END
FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1)
WHERE tbl_inventory.groceryItm = 1ab0
AND InventoryDtl.groceryitmkey = 1
Can anyone shed some light on what I might be missing or a better way to do this???
UPDATE tbl_inventory
SET amt = (SELECT SUM(CostAmt) FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1),
gItmAmount = (SELECT SUM(gItmAmount)
FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1)
WHERE tbl_inventory.groceryItm = 1ab0
AND InventoryDtl.groceryitmkey = 1
In some cases, the gItmAmount is null and the table will not accept a null.
I have tried a case statement but I always get an error. "Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'case'.
Server: Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'end'."
Here is my attempt to use the case statement:
UPDATE tbl_inventory
SET amt = (SELECT SUM(CostAmt) FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1),
CASE when gItmAmount is NULL then gItmAmount = '0' else gItmAmount = (SELECT SUM(gItmAmount) END
FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1)
WHERE tbl_inventory.groceryItm = 1ab0
AND InventoryDtl.groceryitmkey = 1
Can anyone shed some light on what I might be missing or a better way to do this???