5

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 max value for the field "month_wins".

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

But how can i do this?

ToolmakerSteve
  • 11,473
  • 10
  • 75
  • 168
Neka
  • 1,544
  • 3
  • 19
  • 35

2 Answers2

19

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)

SAME AS You can't specify target table 'table_name' for update in FROM clause

Community
  • 1
  • 1
John Woo
  • 249,283
  • 65
  • 481
  • 481
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
)
Akash KC
  • 15,511
  • 5
  • 36
  • 58
  • much more simple and clear! great dude! – Darlan Dieterich Feb 19 '18 at 01:33
  • 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