mysql nested SELECT in UPDATE of same table

Question

Hi,

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

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 price, 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
       )

Answer 2

You could definitely create a stored procedure that takes the id of the new prize winner as an argument.

Then you can call that stored procedure in one line.

I'm using MS SQL here but something similar to:

CREATE PROC UpdatePrize
  @newid int

AS

UPDATE people
SET prize = '' 
WHERE prize = 'Gold'
AND class = ( SELECT class 
              FROM people
              WHERE id = @newid )

UPDATE people
SET prize = 'Gold'
WHERE id = @newid

GO

Answer 3

If you are after a single statement you could use the CASE operator? Not used MySQL but something like:

UPDATE people
SET prize = CASE
              WHEN 'Gold' AND id != @newid THEN ''
              WHEN '' AND id = @newid THEN 'Gold'
            END 
WHERE class = ( 
                SELECT class 
                FROM people
                WHERE id = @newid 
              )

Answer 4

Since you're doing two distinct updates there's no need to do it in one query. If you're afraid of inconsistent data because one of the two queries might fail, you can use transactions and rollback of any of the two queries fails.

Your current query is totally readable and understandable while the posted solutions are far from easy readable.

Answer 5

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;