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?
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';
You will always need the second statement, but as you only have one "Selected" row you could use:
update table set selected = 0 where selected = 1
It looks more like optimizing db structure to me. More info needed for concrete answer though, but check this example to see what I'm talking about:
users:
user_id | user_name | selected_character_id | ...other account data
1 | JamesC | 3
2 | MikeF | 2
characters:
character_id | user_id | ...other character data
1 | 1 |
2 | 2 |
3 | 1 |
You will need JOIN tables to retrieve all data for selected character (fast since it operates on unique ids), but get rid of data duplication (and easier switch).