CODEWITHSUNDEEP
mysqldatabase
user913946
user913946

Reputation: 21

How to delete all rows except the 50 newest

how i can delete all rows in table recentposts

DELETE FROM recentposts WHERE recentposts.`userId` = 12 AND recentposts.`Id` 
NOT IN (SELECT * FROM recentposts WHERE `userId` = 12 ORDER BY viewdate LIMIT 50)

i try many similar to this but they not worked. can someone tell me how i can do this in Mysql.

Upvotes: 1

Views: 316

Answers (2)

xkeshav
xkeshav

Reputation: 54016

DELETE FROM `recentpost`
WHERE WHERE userId = 12 AND id NOT IN (
  SELECT id
  FROM (
    SELECT id
    FROM `table`        
    ORDER BY id DESC
    LIMIT 50
  ) foo
);

PS: only working (MySQL 5.0.67 and upper) AS in earlier versions These are limitations of MySQL.

  • you can't DELETE and SELECT from a given table in the same query.
  • MySQL does not support LIMIT in a subquery.

so for previous version u can come up with is to do this in two stages:

first step

SELECT id FROM mytable ORDER BY id DESC LIMIT n;

Collect the id's and make them into a comma-separated string:

DELETE FROM mytable WHERE id NOT IN ( ...comma-separated string... );

Upvotes: 2

gbn
gbn

Reputation: 432230

What about this?

DELETE FROM recentposts
WHERE
    recentposts.`userId` = 12
    AND
    recentposts.`Id` NOT IN (SELECT Id
        FROM recentposts
        WHERE `userId` = 12
        ORDER BY viewdate DESC LIMIT 50)

Upvotes: 3

Related Questions