Update multiple columns in mysql at once-CodePudding

I am learning MySQL. In already created table, I added a column (class). Now I want to add data in class column. To add data in a single row, I used following code:

update stu SET WHERE id = 1;

Instead of updating every row one by one, I want to update all rows in class column, and I used the following command

update stu SET 
 WHERE id = 2
 WHERE id = 3
 WHERE id = 4
 WHERE id = 5
 WHERE id = 6
 WHERE id = 7

After running the command, following error is showing

update stu SET WHERE id = 2 WHERE id = 3 WHERE id = 4 WHERE id = 5 WHERE id = 6 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 ' WHERE id = 3 WHERE id = 4 WHERE id = 5 class' at line 3 0.000 sec

Can anyone let me know how I can update rows at once?

CodePudding user response：

A single update can generally have only one WHERE clause. However, you may update using a CASE expression:

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;