CODEWITHSUNDEEP
mysql
Ron
Ron

Reputation: 563

What's wrong with my UPDATE CASE query?

My Mysql is mysql Ver 14.14 Distrib 5.1.69, for redhat-linux-gnu (x86_64) using readline 5.1, I have many UPDATE, like 

update player set playorder = 1 where id = 4;
update player set playorder = 2 where id = 5;
update player set playorder = 3 where id = 1;
....

i put them into one UPDATE 

update player set playorder = (CASE
 when id = 4 then 1 
 when id = 5 then 2 
 when id = 1 then 3 END);

I found id=5 playorder is 1, I can't figure out what's the problem. Thanks for any help.

The first sentence of my question is what version about MySQL on my box. So it's better if all experiment on SQL fiddle is on version 5.1.xx. When i update MySQL to 5.5.xx on my box, The odd result disappeared. So I think it's the bug that makes UPDATE CASE sentence fails.I can not build schema on version 5.1.xx on site SQL fiddle.I hope someone can do it.

Upvotes: 0

Views: 135

Answers (1)

Vignesh Kumar A
Vignesh Kumar A

Reputation: 28403

According to MySQL Control Flow Functions:

The first version returns the result where value=compare_value. The second version 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.

Try this

 Update player 
   Set playorder = Case When id = 4 Then 1 
                        When id = 5 Then 2 
                        When id = 1 Then 3 
                   Else Null 
                   END

Upvotes: 1

Related Questions