CODEWITHSUNDEEP
mysqlsql-deletesubquery
Styles2304
Styles2304

Reputation: 177

MySQL Delete All Records Not Selected

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?

Upvotes: 1

Views: 111

Answers (2)

Alma Do
Alma Do

Reputation: 37365

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

Upvotes: 1

gpicchiarelli
gpicchiarelli

Reputation: 452

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

Upvotes: 0

Related Questions