In an effort to speed up my database updates, I’ve been looking for ways to batch some of my updates. CASE seems like the way to go:
mysql> create table bar(a tinyint, b tinyint);
Query OK, 0 rows affected (0.02 sec)
mysql> insert into bar(a) values(1), (2), (3), (4), (5);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from bar;
+------+------+
| a | b |
+------+------+
| 1 | NULL |
| 2 | NULL |
| 3 | NULL |
| 4 | NULL |
| 5 | NULL |
+------+------+
5 rows in set (0.00 sec)
mysql> update bar set b = case a
-> when 1 then 42
-> when 2 then 43
-> when 3 then 44
-> else 45
-> end;
Query OK, 5 rows affected (0.00 sec)
Rows matched: 5 Changed: 5 Warnings: 0
mysql> select * from bar;
+------+------+
| a | b |
+------+------+
| 1 | 42 |
| 2 | 43 |
| 3 | 44 |
| 4 | 45 |
| 5 | 45 |
+------+------+
5 rows in set (0.00 sec)
I see stuff online which warns not to forget the else, otherwise you get a default of null, so I guess I should bear that caveat in mind…