在MySQL的单列中更新多行？
要更新单个列中的多行，请使用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)