MySQL CASE WHEN / THEN / ELSE Update

Question

I am trying to update the LARGE MyISAM table (25 million records) using a CLI script. The table is not locked / is not used by anything else.

I decided instead of making single UPDATE queries for each record, I could also use the CASE function.

The id field is PRIMARY. I suspect the next request should take milliseconds.

 UPDATE `table` SET `uid` = CASE WHEN id = 1 THEN 2952 WHEN id = 2 THEN 4925 WHEN id = 3 THEN 1592 END 

Lo and lo, the request is processed by the CPU and does not end forever.

Then, to my surprise, I found out that the query updates all 25 million rows, putting NULL in rows that I did not specify.

What is the purpose of this? Can I just update MASS for certain rows without updating 25 million rows each time I execute this query? Or do I need to make separate updates and then commit?

Answer 1

try it

 UPDATE `table` SET `uid` = CASE WHEN id = 1 THEN 2952 WHEN id = 2 THEN 4925 WHEN id = 3 THEN 1592 ELSE `uid` END WHERE id in (1,2,3) 

Answer 2

The simplification will be:

 UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3) 

This will only work if uid is really 1, 2, 3.

Source: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_elt

Answer 3

The simplification will be:

 UPDATE `table` SET uid = ELT(id, 2952, 4925, 1592) WHERE id IN (1,2,3) 

Answer 4

This is because you missed the ELSE.

"Returns the result for the first condition, which is true. If there was no matching result, the result is returned after ELSE or NULL if there is no ELSE part." ( http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#operator_case )