136-MySQL5.17 update更新[case when then end]的使用

我的版本是：MySQL5.17

先來看下這張表:

mysql> select * from test;
+----+---------+------+
| id | account | sell |
+----+---------+------+
|  1 | a       |   12 |
|  2 | a       |   12 |
+----+---------+------+
2 rows in set (0.00 sec)

1. 批量更新的SQL語句:

UPDATE 表名 SET 欄位1=CASE 欄位2
WHEN 欄位2值 THEN 欄位1值
WHEN 欄位2值 THEN 欄位1值
END
...

mysql> update test set sell=case
    -> id
    -> when 1 then 20
    -> when 2 then 3
    -> end
    -> where id in (1,2);
Query OK, 2 rows affected (0.01 sec)
Rows matched: 2  Changed: 2  Warnings: 0

這句sql的意思是，更新sell欄位，如果id=1 則sell的值為20，如果id=2 則sell的值為3。

再來看下執行結果：

mysql> select * from test;
+----+---------+------+
| id | account | sell |
+----+---------+------+
|  1 | a       |   20 |
|  2 | a       |    3 |
+----+---------+------+
2 rows in set (0.00 sec)

---

2. 如果要批量更新多個欄位則：

mysql> update test set sell=case id when 1 then 90 when 2 then 8 end,account=case id when 1 then 'Feng' when 2 then 'Scort' end;

Query OK, 2 rows affected (0.04 sec)
Rows matched: 2  Changed: 2  Warnings: 0

結果：

mysql> select * from test;

+----+---------+------+
| id | account | sell |
+----+---------+------+
|  1 | Feng    |   90 |
|  2 | Scort   |    8 |
+----+---------+------+
2 rows in set (0.00 sec)