CODEWITHSUNDEEP
sqlmysql
arthurprs
arthurprs

Reputation: 4627

Update all table rows but top N in Mysql

I was trying to run the following query

UPDATE blog_post SET `thumbnail_present`=0, `thumbnail_size`=0, `thumbnail_data`=''
WHERE `blog_post` NOT IN (
 SELECT `blog_post`
 FROM blog_post
 ORDER BY `blog_post` DESC
 LIMIT 10)

But Mysql doesn't allow 'LIMIT' in an 'IN' subquery.

I think I can make a select to count the table rows and then make an ordered update limited by 'COUNT - 10', but I was wondering if there is a better way.

Thanks in advance.

Upvotes: 1

Views: 1629

Answers (3)

Jon Black
Jon Black

Reputation: 16559

think i understood you right:

update blog_post bp
left outer join
(
select 
 post_id -- what's your PK again ??
from 
 blog_post
order by
 post_id desc limit 10
) latest on latest.post_id = bp.post_id
set 
  bp.thumbnail_present = 0 -- add more fields
where
 latest.post_id is null;

EDIT

I've renamed the fields as you can't seem to take a hint !

update blog_post bp
left outer join
(
select 
 blog_post 
from 
 blog_post
order by
 blog_post desc limit 10
) latest on latest.blog_post = bp.blog_post
set 
  bp.thumbnail_present = 0
where
 latest.blog_post is null;

Upvotes: 1

Chandu
Chandu

Reputation: 82933

Try this:

UPDATE blog_post SET `thumbnail_present`=0, `thumbnail_size`=0, `thumbnail_data`=''
WHERE `blog_post` NOT IN (
    SELECT `blog_post` FROM
    (
     SELECT `blog_post`, (@rowNum := @rowNum + 1) rn
     FROM blog_post, (SELECT @rowNum :=0) b
     ORDER BY `blog_post` DESC
     ) a
 WHERE rn <= 10)

Upvotes: 1

programmer
programmer

Reputation: 918

limit is not supported in subquery, what you will have to do is put the limit on the outer query with update query. this way it will update only 10 rows

Upvotes: 0

Related Questions