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

Is there an id column or Primary Key column? – MikeTWebb Sep 01 '10 at 21:22 — MikeTWebb, Sep 01 '10 at 21:22
@Chris...looks like @Wadhi answered it already. – MikeTWebb Sep 01 '10 at 21:27 — MikeTWebb, Sep 01 '10 at 21:27

OMG Ponies · Answer 1 · 2010-09-01T23:20:16.033

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

edited Sep 01 '10 at 23:20

answered Sep 01 '10 at 21:26

OMG Ponies

325,700
82
523
502

Thanks this looks perfect, anyway I can set all from the joined table to be 1 in the same query or do I have to do another? – Chris Sep 01 '10 at 22:40
I ran into a small problem implementing your query - I updated question - could you please take a look? – Chris Sep 01 '10 at 22:49
watch out for the order by t.id desc limit 10 - you did say "I want to reset all counts to zero except for the 10 rows with the top values" so maybe that should be order by desc limit 10 – Jon Black Sep 01 '10 at 22:50
@chris: It was because of a typo in the JOIN - `LIMIT 10) t2 ON t2.id = t.id` was supposed to be: `LIMIT 10) t2 ON t2.id = t1.id`. I corrected my answer. – OMG Ponies Sep 01 '10 at 23:21
Ah cheers, I marked your answer up but already accepted f00's 'cos it worked first. Thanks for the help anyway. – Chris Sep 02 '10 at 08:12

Jon Black · Accepted Answer · 2010-09-01T22:59:21.660

1

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;

edited Sep 01 '10 at 22:59

answered Sep 01 '10 at 21:28

Jon Black

16,223
5
43
42

score 0 · Answer 3 · answered Sep 01 '10 at 21:20

0

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)

answered Sep 01 '10 at 21:20

Wadih M.

12,810
7
47
57

MySQL error 1093 - can't specify target table for update in FROM clause. For more info, see: http://stackoverflow.com/questions/3620940/deleting-a-row-based-on-the-max-value/3621005#3621005 – OMG Ponies Sep 01 '10 at 21:22
@OMG Ponies: Thanks OMG Ponies. How would you correct the code? – Wadih M. Sep 01 '10 at 21:25
If you put a subquery between the UPDATE and the subquery returning data, MySQL will accept it - there's an example in the link I provided. – OMG Ponies Sep 01 '10 at 21:28

MySQL UPDATE - Selective update

3 Answers3