MySQL Update CASE 用 "0"更新字段

Question

我在使用 CASE 语句更新 SQL 表时遇到问题。

SELECT number,amount,minimuminventory FROM artikel WHERE number=17;

+--------+--------+------------------+| number | amount | minimuminventory |+--------+--------+------------------+|     17 |     10 |                0 |+--------+--------+------------------+

I have an amount of 10 but when I update my table:

UPDATE artikel 
SET amount = CASE WHEN amount - minimuminventory - 2 < 0 
                  THEN amount=amount-2 
                  ELSE amount=99 
             END 
WHERE artnr=17;

Query OK, 1 rows affected (0,01 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql 更新我的表，集合数量为 0

SELECT number,amount,minimuminventory FROM artikel WHERE number=17;

+--------+--------+------------------+| number | amount | minimuminventory |+--------+--------+------------------+|     17 |      0 |                0 |+--------+--------+------------------+

你看到问题了吗？

Answer 1

amount = CASE WHEN amount - minimuminventory - 2 < 0 THEN amount=amount-2 ELSE amount=99 END

CASE 语句中的值是 amount 列的预期值，但您在它的 THEN 和 ELSE 部分中再次执行 amount=x。将其更改为:

amount = CASE WHEN amount - minimuminventory - 2 < 0 THEN amount-2 ELSE 99 END