SQL优化查询

Question

I have a table that has the following records:

    ID | Username | Selected |
   ----------------------------
    1  |  JamesC  |     1    |
    2  |  MikeF   |     0    |
    3  |  JamesC  |     0    |

I wish to have Selected be true for only 1 row where the username is the same. So for example when I set ID = 3 to be Selected = true I wish to setID =1 to have Selected = false as well as any other ID's with the same username.

Right now i'm doing this,

//set all to 0
update table set selected = 0 where username = '$username'

//set unique row to true
update table set selected = 1 where username = '$username' and ID = '$ID';

Is there a better more concise way of achieving the same effect?

Answer 1

As it was said - not very nice db structure, it is better to have table with unique names and ID of selected item, anyway, you can go with this single query:

update table set selected=IF(id = '$ID', 1, 0) where username = '$username';

also, try a possible faster variant (test both via explain):

update table set selected=IF(id <> '$ID', 0, 1) where username = '$username';