Ever wanted to do some update on multiple rows with different value or on different condition in a single SQL query? Usually, this can be done by executing some queries in a row as needed. But with MySQL we can do it in a single SQL query. How come?

For example, I have the following table:

mysql> SELECT * FROM status;
+---------+--------+
| name    | active |
+---------+--------+
| Wendy   | y      |
| Riky    | n      |
| Julia   | n      |
| Andy    | n      |
| Leandra | n      |
+---------+--------+
5 rows in set (0.00 sec)

In this example, I would like to change the active status of person named 'Andy' to 'y' and change the rest to 'n'. By using usual UPDATE clause we could do the above case by using two queries.

-- Update active value to 'n' on row which have active value 'y'
UPDATE status SET active = 'n' WHERE active = 'y'

-- Update active value to 'y' on row which have name value 'Andy'
UPDATE status SET active = 'y' WHERE name = 'Andy'

We can do the above case by using a single SQL query with CASE clause:

UPDATE status SET active = CASE WHEN name LIKE 'Andy' 
THEN 'y' ELSE 'n' END;

By using the above SQL, you will get:

mysql> SELECT * FROM status; 

+---------+--------+ 
| name    | active | 
+---------+--------+ 
| Wendy   | n      | 
| Riky    | n      | 
| Julia   | n      | 
| Andy    | y      | 
| Leandra | n      | 
+---------+--------+ 
5 rows in set (0.00 sec)

Well, that's it! You can modify the SQL code to suit your needs.