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

User · Answer

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

User · Answer

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

User · Answer

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

[mysql] MySQL update CASE WHEN/THEN/ELSE

Examples related to mysql