I'm trying to make this query work but I'm unable to update the desired field. This is my code:
UPDATE inventory
SET inventoryCount = CASE
WHEN itemId = 1 THEN inventoryCount+1
WHEN itemId = 2 THEN inventoryCount+3
WHEN itemId = 3 THEN inventoryCount+6
ELSE inventoryCount
END
WHERE itemId IN (1,2,3) AND outletId = 23 AND companyId = 2
I need to add up X amount to the current amount in inventoryCount, some how it seems to ignore it and nothing changes.
Am I doing something wrong?
Your query has syntax errors, so it's probably not even executing correctly;
WHEN itemId = 1 THEN inventoryCount+1, <-- comma should not be there
WHEN itemId = 2 THEN inventoryCount+3, <-- comma should not be there
WHEN itemId = 3 THEN inventoryCount+6, <-- comma should not be there
If you remove the extra commas, the query will execute correctly;