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(
)
posted at: 14:11 | path: /mysql | permanent link to this entry
Alex
You will most likely get a "Case not found for CASE statement" error.
So don't forget that else !
strcmp
you get nice table scans with that, don't you? a WHERE should help here.
in your simple case you also could use ELT().
Olaf van derSpek
You could (ab)use insert ... on duplicate key update ...
See also http://bugs.mysql.com/bug.php?id=34228
Roland Bouman
Hi!
This is actually a CASE *expression* (you can also call it "CASE operator" or like the reference manual, CASE function). It is however *not* a CASE statement.
CASE statements are stored procedure statement - they can appear only inside stored procedures. Their THEN branches can contain a list of SQL statements, and when a branch is chosen, the statements are executed.
The CASE expression you describe here is a value-expression. It is evaluated, and the resulting value is used in place of the expression. Each WHEN branch can list at most one expression which is evaluated when a branc is chosen.
Both CASE expressions as well as CASE statments come in two forms: simple and searched. In your example you are using a simple case expression: an expression appears immediately after the CASE keyword, and an expression appears after each WHEN keyword to which that is compared. In a searched case, there is no expresion after the CASE keyword. Instead, in a searched case, a condition (boolean expression) appears after each WHEN keyword, and the THEN part of the first WHEN branch whose condition evaluates to true, is activated.
kind regards,
Roland