我正在尝试使此查询正常工作,但无法更新所需的字段。这是我的代码:

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


我需要将X金额总计为inventoryCount中的当前金额,以某种方式忽略它,并且没有任何变化。

难道我做错了什么?

最佳答案

您的查询存在语法错误,因此它甚至可能无法正确执行;

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


如果删除多余的逗号,查询将正确执行。

An SQLfiddle to show the working query

08-08 08:07