What's wrong with my UPDATE CASE query?

Question

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.

possible duplicate of [MySQL update CASE WHEN/THEN/ELSE](http://stackoverflow.com/questions/12754470/mysql-update-case-when-then-else) — Jens, Jul 01 '14 at 07:01
There is no any problem in query it working fine check http://sqlfiddle.com/#!2/73b54/1 — Sadikhasan, Jul 01 '14 at 07:09

score 1 · Answer 1 · edited Aug 16 '14 at 03:21

1

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

edited Aug 16 '14 at 03:21

answered Jul 01 '14 at 07:06

Vignesh Kumar A

27,863
13
63
115

playorder is null where id is 1, playorder is null where id is 4, and playorder is 1 where id is 5. – Ron Jul 01 '14 at 07:21
1

@Ron Check your Column (Id) values well because it's working [fiddle](http://sqlfiddle.com/#!2/e2226/1) – Vignesh Kumar A Jul 01 '14 at 07:31
1

@Ron If id column is `Varchar` field then check ant trailing or leading space around the valeues – Vignesh Kumar A Jul 01 '14 at 07:34
Maybe it's bug of version 5.1.69. fiddle's mysql is 5.5.32. – Ron Jul 01 '14 at 07:35
@Kumar could you build schema on 5.1.61? From my ip, building is very slow. – Ron Jul 01 '14 at 08:02

What's wrong with my UPDATE CASE query?

1 Answers1