MySQL UPDATE - Selective update

Question

First up, apologies for the awful title I couldn't think of a better way to articulate my issue. (Feel free to suggest better altnernatives)

Basically I have a table with a "count" column. I want to reset all counts to zero except for the 10 rows with the top values. I want them to be reset to 0.

How do I achieve this without writing multiple queries?

Update I have my query as the following now

UPDATE covers AS t1 
  LEFT JOIN (SELECT t.cover_id 
               FROM covers t 
               ORDER BY t.cover_views DESC 
               LIMIT 10) AS t2 ON t2.id = t.id
   SET cover_views = 0
   WHERE t2.id IS NULL

I get the error #1054 - Unknown column 't2.id' in 'where clause' - any idea why?

I also tried the following with the same result

UPDATE covers t1 
  LEFT JOIN (SELECT t.cover_id 
               FROM covers t 
               ORDER BY t.cover_views DESC 
               LIMIT 10) t2 ON t2.id = t.id
   SET t1.cover_views = 0
   WHERE t2.id IS NULL

Answer 1

You can use a subquery:

update A set count = 0 where A.id not in 
(select id from A order by count desc limit 10)

Answer 2

Use:

UPDATE TABLE t1 
  LEFT JOIN (SELECT t.id 
               FROM TABLE t 
           ORDER BY t.id DESC 
              LIMIT 10) t2 ON t2.id = t1.id
   SET TABLE.count = 0
 WHERE t2.id IS NULL

Answer 3

try:

update <table> t 
left outer join 
(
select id from <table> order by <counter> desc limit 10
) c on c.id = t.id 
set 
 <counter> = 0
where 
 c.id is null;