SQL optimizing query

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?

What SQL Database are you using? Please tag with that.

Shiva
– Shiva

2013-12-04 23:42:44 +00:00
Commented Dec 4, 2013 at 23:42 — Shiva
– Shiva, Commented Dec 4, 2013 at 23:42
@shiva sorry am using MySql

Edward
– Edward

2013-12-04 23:43:18 +00:00
Commented Dec 4, 2013 at 23:43 — Edward
– Edward, Commented Dec 4, 2013 at 23:43
Another table with selected whatever they ares?

Tony Hopkinson
– Tony Hopkinson

2013-12-04 23:44:24 +00:00
Commented Dec 4, 2013 at 23:44 — Tony Hopkinson
– Tony Hopkinson, Commented Dec 4, 2013 at 23:44

Iłya Bursov · Accepted Answer · 2013-12-05 01:52:28Z

2

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';

answered Dec 5, 2013 at 1:52

Iłya Bursov

24.3k4 gold badges36 silver badges68 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Edward Over a year ago

Can you explain why this structure is not optimal? Yes it's not BCNF but is that better then doing a join to find selected values for every row?

Iłya Bursov Over a year ago

@Edward what joins? just table with user names and for each user name only one int field with selectedId, so you will be able just to use update table set selected=$ID where username=$username

shudder · Accepted Answer · 2013-12-05 01:02:15Z

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).

boedah · Accepted Answer · 2013-12-04 23:44:31Z

0

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

answered Dec 4, 2013 at 23:44

boedah

542 bronze badges

2 Comments

Jason Heo Over a year ago

OP is asking "true for only 1 row where the username is the same", it this the answer for OP? then could you explain for OP and me?

boedah Over a year ago

You are right, missed the "where username is the same". It also seems to me the OP falls into the X/Y problem...

Collectives™ on Stack Overflow

SQL optimizing query

3 Answers 3

2 Comments

Comments

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Related