MySQL update CASE WHEN/THEN/ELSE

Question

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?

When you dont specify `else` in `case` statement default is `null` — rs., Oct 05 '12 at 21:48

rs. · Accepted Answer · 2012-10-06T00:55:57.877

222

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)

edited Oct 06 '12 at 00:55

answered Oct 05 '12 at 21:46

rs.

26,707
12
68
90

5

Code-only answers are low value on Stack Overflow because they do very little to educate/empower thousands of future researchers. Why write the `ELSE` if it cannot logically be reached? – mickmackusa Dec 29 '20 at 19:34
2

@mickmackusa adding an else anyway is good for guarding against mishaps when the code is changed e.g. the implementer will change the WHERE clause. This si similar to adding unreachable `break;`s to switch-case statements after a `return` statement. Regarding the code-only answer, I agree. The answerer could have given more details about the reasoning behind the code. – Claude Kirke Mar 19 '21 at 09:21

Ross Smith II · Answer 2 · 2020-12-29T20:14:10.200

9

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.

edited Dec 29 '20 at 20:14

answered Oct 05 '12 at 21:50

Ross Smith II

11,799
1
38
43

Did the simpler idea by any chance come from [this comment](https://stackoverflow.com/questions/12754470/mysql-update-case-when-then-else/19828429#comment91241641_19828429) by [BringerOfTheDawn](https://stackoverflow.com/users/3252596/bringerofthedawn) stating "I combined this with [FIELD](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_field) eg `UPDATE table SET uid = ELT(FIELD(id, 1,2,3), 2952, 4925, 1592) WHERE id IN (1,2,3)`"? – Scratte Dec 29 '20 at 20:21
Your "simpler" version will only work if uid is indeed 1, 2, 3. (Source: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_elt) – godiane Nov 07 '13 at 05:11
I combined this with [FIELD](https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_field) eg `UPDATE table SET uid = ELT(FIELD(id, 1,2,3), 2952, 4925, 1592) WHERE id IN (1,2,3)` – BringerOfTheDawn Sep 03 '18 at 08:32

score 4 · Answer 3 · answered Oct 05 '12 at 21:51

4

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)

answered Oct 05 '12 at 21:51

alex

3,682
3
21
22

4

even if I specified an ELSE.. Wouldn't it try to update the rest? I still do not want to update 25 million records when I only need to update 3. Placing a WHERE clause solves the problem. – nick Oct 05 '12 at 21:53
This answer would resolve the question but not in a performant / direct / best practice way. – mickmackusa Dec 29 '20 at 19:39

MySQL update CASE WHEN/THEN/ELSE

3 Answers3

Linked

Related