Update row that has the current highest (max) value of one field [closed]

Question

I have this SQL query, which is wrong. I want to update the field "prevmonth" in the wins table, but only for the record that has the maximum value for the field "month_wins".

UPDATE wins
SET prevmonth_top=1
WHERE month_wins = (SELECT MAX(month_wins) FROM wins)

Error message:

#1093 - You can't specify target table 'wins' for update in FROM clause

But how can I do this?

Answer 1

Try this trick:

UPDATE wins
SET prevmonth_top=1
ORDER BY month_wins DESC
LIMIT 1

Or something like this:

UPDATE IGNORE giveaways
SET winner = 1
WHERE month_wins = (select maxID from (SELECT MAX(ID) maxID FROM giveaways) as t)

It is the same as You can't specify target table 'table_name' for update in FROM clause.

Answer 2

Run the following query. It may help:

UPDATE wins
SET prevmonth_top=1 WHERE month_wins =
(
    SELECT month_wins FROM (SELECT MAX(month_wins) FROM wins) AS month_wins
)