我的版本是：MySQL5.17
先來(lái)看下這張表:
mysql> select * from test;
+----+---------+------+
| id | account | sell |
+----+---------+------+
| 1 | a | 12 |
| 2 | a | 12 |
+----+---------+------+
2 rows in set (0.00 sec)
1. 批量更新的SQL語(yǔ)句:
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族操。
再來(lái)看下執(zhí)行結(jié)果：
mysql> select * from test;
+----+---------+------+
| id | account | sell |
+----+---------+------+
| 1 | a | 20 |
| 2 | a | 3 |
+----+---------+------+
2 rows in set (0.00 sec)
2. 如果要批量更新多個(gè)字段則：
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
結(jié)果：
mysql> select * from test;
+----+---------+------+
| id | account | sell |
+----+---------+------+
| 1 | Feng | 90 |
| 2 | Scort | 8 |
+----+---------+------+
2 rows in set (0.00 sec)
