6

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?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Neka
  • 1,574
  • 4
  • 22
  • 36

2 Answers2

21

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.

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
John Woo
  • 258,903
  • 69
  • 498
  • 492
5

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
)
Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Akash KC
  • 16,057
  • 6
  • 39
  • 59
  • much more simple and clear! great dude! – Darlan Dieterich Feb 19 '18 at 01:33
  • 1
    Minor nit: the use of `month_wins` in so many places makes this answer less clear than it could be. I'd change the nested selects to something like `(SELECT maxWin FROM (SELECT MAX(month_wins) maxWin FROM wins) AS t)`. This clarifies that `maxWin` is the max value, not another reference to the field `month_wins`, and that `t` is an arbitrary temp name. – ToolmakerSteve Jan 17 '19 at 00:57