Update multiple columns in mysql at once

October 27, 2022

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 class="5th" 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 
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

After running the command, following error is showing

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

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

>Solution :

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;