1

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);
James Z
  • 12,209
  • 10
  • 24
  • 44
Chitrasen
  • 49
  • 1
  • 6
  • 1
    Does this answer your question? [Mysql delete statement with limit](https://stackoverflow.com/questions/7142097/mysql-delete-statement-with-limit) – Zoli Szabó Dec 13 '21 at 09:32
  • You could use join or wrap the select query in another outer query so you don't get the `You can't specify target table 'audit_logs ' for update in FROM clause` error – Ergest Basha Dec 13 '21 at 09:45
  • Either use stored procedure instread of single query for insertion or insert 100k empty rows from the same beginning and update the oldest row instead of insertion (you must have the autoutilized column with updation datetime). – Akina Dec 13 '21 at 09:49

1 Answers1

0

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
                         );

Read more on : https://dev.mysql.com/doc/refman/8.0/en/update.html

Ergest Basha
  • 7,870
  • 4
  • 8
  • 28