一次更新mysql中的多个列

lsmepo6l 于 2022-10-31 发布在 Mysql

关注(0)|答案(1)|浏览(205)

我正在学习MySQL。在已经创建的表中，我添加了一个列（类）。现在我想在类列中添加数据。为了在单行中添加数据，我使用了以下代码：
update stu SET class="5th" WHERE id = 2 class="6th" WHERE id = 3 class="6th" WHERE id = 4 class="7th" WHERE id = 5 class="7th" WHERE id = 6 class="8th" WHERE id = 7 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'class="6th" WHERE id = 3 class="6th" WHERE id = 4 class="7th" WHERE id = 5 class' at line 3 0.000 sec
我希望更新class列中的所有行，而不是逐个更新每一行，我使用了以下命令

update stu SET 
class="5th" WHERE id = 2
class="6th" WHERE id = 3
class="6th" WHERE id = 4
class="7th" WHERE id = 5
class="7th" WHERE id = 6
class="8th" WHERE id = 7

运行命令后，显示以下错误
update stu SET class="5th" WHERE id = 2 class="6th" WHERE id = 3 class="6th" WHERE id = 4 class="7th" WHERE id = 5 class="7th" WHERE id = 6 class="8th" WHERE id = 7 Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'class="6th" WHERE id = 3 class="6th" WHERE id = 4 class="7th" WHERE id = 5 class' at line 3 0.000 sec个
有人能告诉我如何立即更新行吗？

mysql

来源：https://stackoverflow.com/questions/74225448/update-multiple-columns-in-mysql-at-once

1条答案

按热度按时间

dbf7pr2w1#

一次更新通常只能有一个WHERE子句。但是，您可以使用CASE表达式进行更新：

UPDATE stu
SET class = CASE id WHEN 2 THEN '5th'
                    WHEN 3 THEN '6th'
                    WHEN 4 THEN '6th'
                    WHEN 5 THEN '7th'
                    WHEN 6 THEN '7th'
                    WHEN 7 THEN '8th' END
WHERE id BETWEEN 2 AND 7;

赞(0）回复(0）举报 2022-10-31

我来回答

一次更新mysql中的多个列

1条答案

相关问题

热门标签

最新问答