Blog

Discovering the CASE statement

  • Post author:
  • Post category:Mysql

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…

Tags: ,