要更新单个列中的多行,请使用CASE语句。让我们首先创建一个表-mysql> create table updateMultipleRowsDemo
-> (
-> StudentId int,
-> StudentMathScore int
-> );
以下是使用insert命令在表中插入记录的查询-mysql> insert into updateMultipleRowsDemo values(10001,67);
mysql> insert into updateMultipleRowsDemo values(10002,69);
mysql> insert into updateMultipleRowsDemo values(10003,89);
mysql> insert into updateMultipleRowsDemo values(10004,99);
mysql> insert into updateMultipleRowsDemo values(10005,92);
以下是使用select语句显示表中所有记录的查询-mysql> select * from updateMultipleRowsDemo;
这将产生以下输出-+-----------+------------------+
| StudentId | StudentMathScore |
+-----------+------------------+
| 10001 | 67 |
| 10002 | 69 |
| 10003 | 89 |
| 10004 | 99 |
| 10005 | 92 |
+-----------+------------------+
5 rows in set (0.00 sec)
这是在MySQL的单列中更新多行的查询-mysql> UPDATE updateMultipleRowsDemo
-> SET StudentMathScore= CASE StudentId
-> WHEN 10001 THEN 45
-> WHEN 10002 THEN 52
-> WHEN 10003 THEN 67
-> END
-> WHERE StudentId BETWEEN 10001 AND 10003;
Rows matched: 3 Changed: 3 Warnings: 0
让我们检查值是否已更新-mysql> select * from updateMultipleRowsDemo;
这将产生以下输出+-----------+------------------+
| StudentId | StudentMathScore |
+-----------+------------------+
| 10001 | 45 |
| 10002 | 52 |
| 10003 | 67 |
| 10004 | 99 |
| 10005 | 92 |
+-----------+------------------+
5 rows in set (0.00 sec)