updating the few highest rows in a table using mysql 5.1.41

Question

I have a table with several hundred rows. I want to take the top 20 based on the value of column num and replace num with a number.

What I tried to do (which didn't work) was:

UPDATE table
SET num = 95
WHERE id IN (SELECT id FROM table ORDER BY id DESC LIMIT 20)

Any suggestions?

Answer 1

Try a small change:

UPDATE table
SET num = 95
WHERE id IN (SELECT id FROM table ORDER BY num DESC LIMIT 20)

If you want to get the top 20 num's, you need to sort by num's.

Answer 2

There's no need for the subquery - you can use LIMIT in an UPDATE statement on MySQL:

  UPDATE table
     SET num = 95
ORDER BY id DESC
   LIMIT 20

It's unclear what you want to order by - I used id based on your subquery, but in case it needs to be by the num value:

  UPDATE table
     SET num = 95
ORDER BY num DESC
   LIMIT 20