mysql nested SELECT in UPDATE of same table

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;

UPDATE peopleSET    prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' ENDWHERE  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 peopleSET    prize = CASE WHEN id = @lucky THEN 'Gold' ELSE 'Silver' ENDWHERE  id = @lucky       OR (class, prize) =       (       SELECT  class, 'Gold'       FROM    people       WHERE   id = @lucky       )

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

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