Delete records from table, if it exceeds 100k records-CodePudding

I have a condition, where in Audit logs, if the records exceeds 100k, then delete the previous old records, I dont want to delete all the 100k records, but want to delete only old records, I want to maintain the latest 100k records.

Below is a query i have tried, please anyone help me, how to prepare the query.

DELETE FROM  audit_logs where 
id not in (SELECT id from audit_logs order by ID DESC LIMIT 100000);

CodePudding user response：

You could wrap into another select the subquery;

DELETE FROM  audit_logs 
WHERE  id not in  (SELECT t1.id 
                   FROM ( SELECT id 
                          FROM audit_logs 
                          ORDER BY ID DESC 
                          LIMIT 100000
                         ) as t1
                    );

Or use NOT EXISTS :

    DELETE FROM  audit_logs  a1 
    WHERE  NOT EXISTS   ( SELECT * 
                          FROM ( SELECT id 
                                 FROM audit_logs a2
                                 ORDER BY ID DESC 
                                 LIMIT 100000
                               ) as t1
                         );