MySQL update CASE WHEN/THEN/ELSE [duplicate]

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

I figured instead of doing single UPDATE queries for each record, I might as well utilize the CASE feature.

The id field is PRIMARY. I suspect the following query 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 behold, the query hogs the CPU and doesn't finish in forever.

Then, to my surprise, I found out that the query is updating all the 25 million rows, placing a NULL on rows that I didn't specify.

What is the purpose of that? Can I just do a MASS update on specific rows without updating 25 million rows every time I execute this query? Or do I have to do individual updates and then commit?

Solution 1:

Try this

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)

Solution 2:

If id is sequential starting at 1, the simplest (and quickest) would be:

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

As ELT() returns the Nth element of the list of strings: str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments.

Clearly, the above code only works if id is 1, 2, or 3. If id was 10, 20, or 30, either of the following would work:

UPDATE `table` 
SET uid = CASE id 
WHEN 10 THEN 2952 
WHEN 20 THEN 4925 
WHEN 30 THEN 1592 END CASE 
WHERE id IN (10, 20, 30)

or the simpler:

UPDATE `table` 
SET uid = ELT(FIELD(id, 10, 20, 30), 2952, 4925, 1592) 
WHERE id IN (10, 20, 30)

As FIELD() returns the index (position) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

Solution 3:

That's because you missed ELSE.

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