136-MySQL5.17 update更新[case when

我的版本是：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)