mysql nested SELECT in UPDATE of same table

Question

Essentially I need to do something like this.... this is just an example... but the syntax of the first query doesn't work in MySQL

update people set prize = '' 
where prize = 'Gold' and class = (select class from people where id = person_id);
update people set prize = 'Gold' where id = <id>;

Only one person can have the Gold prize in any class. I only know the person_id of the person who receives the Gold prize.

I am trying to blank out any previous Gold prize winners in the same class as person_id in the first query. Then set the new Gold winner in the second.

I believe I need to use some type of inner join, but I'm not 100% sure on this.

What would be even smarter if I could do the whole lot in one query!

Can anyone lend advice?

Thanks :)

Answer 1

It's not always best to do complex updates in a single SQL query. In fact, it could be more efficient to run two simple queries. So be sure to benchmark both solutions.

MySQL supports an extension to UPDATE syntax for multi-table update. You can perform a JOIN as part of the update instead of using subqueries.

Then you can use the IF() function (or CASE) to change the prize value to different values conditionally.

So if you absolutely must use a single query, try something like this:

UPDATE people p1 JOIN people p2 
  ON (p1.class = p2.class AND p2.id = <person_id>)
SET prize = IF(p1.id = <person_id>, 'Gold', '')
WHERE p1.id = <person_id> OR p1.prize = 'Gold';

Or this alternative:

UPDATE people p1 JOIN people p2 
  ON (p1.class = p2.class AND p2.id = <person_id>)
SET p1.prize = CASE 
  WHEN p1.id = <person_id> THEN 'Gold'
  WHEN p1.prize = 'Gold' THEN ''
  ELSE p1.prize -- other cases are left as is
 END CASE;

Answer 2

UPDATE people
SET    prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' END
WHERE  class = (
       SELECT  class
       FROM    people
       WHERE   id = @lucky
       )

This will grant all users with a Silver prize, except the @lucky one who gets the Gold.

If you only need to update the @lucky and the ex-champion, issue the following:

UPDATE people
SET    prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' END
WHERE  id = @lucky
       OR (class, prize) =
       (
       SELECT  class, 'Gold'
       FROM    people
       WHERE   id = @lucky
       )