Error when attempting to update a table

[rosn459]

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???

 

[SQLDenis]

lookup COALESCE and ISNULL in BOL
note I did not put it around CostAmt since you didn't indicate that that was breaking
try this

UPDATE tbl_inventory
SET amt = (SELECT SUM(CostAmt) FROM InventoryDtl
WHERE groceryItm = 1ab0 AND groceryitmkey = 1),
    gItmAmount = (SELECT COALESCE(SUM(gItmAmount),0)
FROM InventoryDtl
WHERE groceryItm = 1ab0  AND groceryitmkey = 1)
WHERE tbl_inventory.groceryItm = 1ab0
    AND InventoryDtl.groceryitmkey = 1

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[rosn459]

SQLDenis,

I get my original error of "insert the value NULL into column gItmAmount

 

[SQLDenis]

But you are not updating that amount you are updating amt

UPDATE tbl_inventory
SET amt.....


Is there a trigger on the table perhaps?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/