MySQL UPDATE CASE没有按预期工作
问题描述：
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?
我正在尝试使此查询有效，但我无法更新所需的字段。 这是我的代码： p>
UPDATE inventory
SET inventoryCount = CASE
WHEN itemId = 1那么inventoryCount + 1
WHEN itemId = 2那么inventoryCount + 3
WHEN itemId = 3那么inventoryCount + 6
ELSE inventoryCount
END
WHERE itemId IN（1,2,3）AND outletId = 23 AND companyId = 2
code> pre>
I 需要将 X code>金额加到 inventoryCount code>中的当前金额，一些似乎忽略它并且没有任何变化。 p>
Am 我做错了什么？ p>
div>
答
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;