MySQL Delete All Records Not Selected

Question

I'm trying to delete all entries except the 25 most recent based on the timestamp. I tried this:

DELETE FROM `chat` WHERE `IndexNo` NOT IN(SELECT `IndexNo` FROM `chat` ORDER BY `Timestamp` DESC LIMIT 25)

That generates this error:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

Is there a way to accomplish this?

Unless I'm very out-of-touch, it really couldn't matter which version they're using. — Strawberry, Aug 29 '13 at 12:57

score 1 · Accepted Answer · answered Aug 29 '13 at 13:05

You can not rely on LIMIT in IN/ALL/ANY/SOME subqueries, but instead you can use multi-delete syntax:

DELETE 
  `chat` 
FROM 
  `chat` 
    LEFT JOIN (SELECT `IndexNo` FROM `chat` ORDER BY `Timestamp` DESC LIMIT 25) AS `selected`
      ON `chat`.`IndexNo`=`selected`.`IndexNo` 
WHERE 
  `selected`.`IndexNo` IS NULL;

-according to manual, this will work since MySQL 5.0

score 0 · Answer 2 · edited May 23 '17 at 12:23

0

It is a more general issue about subquery I guess. Try to take a look here: Problem with LIMIT & IN/ALL/ANY/SOME subquery

edited May 23 '17 at 12:23

Community

1
1

answered Aug 29 '13 at 12:55

gpicchiarelli

454
6
16

MySQL Delete All Records Not Selected

2 Answers2