CODEWITHSUNDEEP
phpmysqlsql-update
Crash Override
Crash Override

Reputation: 461

MySQL UPDATE CASE not working as expected

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?

Upvotes: 0

Views: 123

Answers (1)

Joachim Isaksson
Joachim Isaksson

Reputation: 180927

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;

An SQLfiddle to show the working query.

Upvotes: 3

Related Questions