Discovering the CASE statement

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 for this post: mysql()

Comment on this post

Syndicated 2008-05-12 14:11:00 from stillhq.com : Mikal, a geek from Canberra living in Silicon Valley