themaverick
New Member
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?UpdateI have my query as the following now\[code\]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\[/code\]I get the error \[code\]#1054 - Unknown column 't2.id' in 'where clause'\[/code\] - any idea why?I also tried the following with the same result\[code\]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\[/code\]