CODEWITHSUNDEEP
sqlmysql
tipu
tipu

Reputation: 9604

updating the few highest rows in a table using mysql 5.1.41

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?

Upvotes: 1

Views: 94

Answers (2)

OMG Ponies
OMG Ponies

Reputation: 332681

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

Upvotes: 3

Byron Sommardahl
Byron Sommardahl

Reputation: 13012

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.

Upvotes: 0

Related Questions