Update Multiple Rows with Different Values in a Single SQL Query

Imagine that you have to update large number of rows in a table and the update values might be different for each row based on some condition. Executing 100 sql queries to update 100 rows will be unwise, instead you can use a single sql query to update them all:

mysql> DESC tbl_name;
+----------+------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------+------------+------+-----+---------+----------------+
| id | bigint(20) | NO | PRI | NULL | auto_increment |
| col_int | int(11) | NO | | NULL | |
| col_char | char(1) | NO | | NULL | |
+----------+------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> SELECT * FROM tbl_name;
+----+---------+----------+
| id | col_int | col_char |
+----+---------+----------+
| 1 | 10 | A |
| 2 | 20 | B |
+----+---------+----------+
2 rows in set (0.00 sec)

mysql> UPDATE tbl_name SET
-> col_int = CASE id WHEN 1 THEN 30 WHEN 2 THEN 40 END,
-> col_char = CASE id WHEN 1 THEN 'X' WHEN 2 THEN 'Y' END
-> WHERE id IN (1,2);
Query OK, 0 rows affected (0.04 sec)
Rows matched: 2 Changed: 0 Warnings: 0

mysql> SELECT * FROM tbl_name;
+----+---------+----------+
| id | col_int | col_char |
+----+---------+----------+
| 1 | 30 | X |
| 2 | 40 | Y |
+----+---------+----------+
2 rows in set (0.00 sec)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s