Truncate with condition

Question

truncate  - this resets the entire table  is there a way via truncate to reset particular records check conditions   For ex  i want to reset all the data and keep last 30 days inside the table   Thanks

User · Accepted Answer

No  TRUNCATE is all or nothing  You can do a DELETE FROM  lt table gt  WHERE  lt conditions gt  but this loses the speed advantages of TRUNCATE

User · Answer

You can simply export the table with a query clause using datapump and import it back with table exists action replace clause  Its will drop and recreate your table and take very less time  Please read about it before implementing

User · Answer

As a response to your question   i want to reset all the data and keep last 30 days inside the table    you can create an event  Check https   dev mysql com doc refman 5 7 en event-scheduler html  For example   CREATE EVENT DeleteExpiredLog ON SCHEDULE EVERY 1 DAY DO DELETE FROM log WHERE date  lt  DATE SUB NOW    INTERVAL 30 DAY     Will run a daily cleanup in your table  keeping the last 30 days data available

User · Answer

The short answer is no  MySQL does not allow you to add a WHERE clause to the TRUNCATE statement   Here s MySQL s documentation about the TRUNCATE statement   But the good news is that you can  somewhat  work around this limitation   Simple  safe  clean but slow solution using DELETE  First of all  if the table is small enough  simply use the DELETE statement  it had to be mentioned    1  LOCK TABLE my table WRITE  2  DELETE FROM my table WHERE my date lt DATE SUB NOW    INTERVAL 1 MONTH   3  UNLOCK TABLES    The LOCK and UNLOCK statements are not compulsory  but they will speed things up and avoid potential deadlocks   Unfortunately  this will be very slow if your table is large    and since you are considering using the TRUNCATE statement  I suppose it s because your table is large   So here s one way to solve your problem using the TRUNCATE statement   Simple  fast  but unsafe solution using TRUNCATE  1  CREATE TABLE my table backup AS       SELECT   FROM my table WHERE my date gt  DATE SUB NOW    INTERVAL 1 MONTH   2  TRUNCATE my table  3  LOCK TABLE my table WRITE  my table backup WRITE  4  INSERT INTO my table SELECT   FROM my table backup  5  UNLOCK TABLES  6  DROP TABLE my table backup    Unfortunately  this solution is a bit unsafe if other processes are inserting records in the table at the same time    any record inserted between steps 1 and 2 will be lost the TRUNCATE statement resets the AUTO-INCREMENT counter to zero   So any record inserted between steps 2 and 3 will have an ID that will be lower than older IDs and that might even conflict with IDs inserted at step 4  note that the AUTO-INCREMENT counter will be back to it s proper value after step 4     Unfortunately  it is not possible to lock the table and truncate it   But we can  somehow  work around that limitation using RENAME   Half-simple  fast  safe but noisy solution using TRUNCATE  1  RENAME TABLE my table TO my table work  2  CREATE TABLE my table backup AS      SELECT   FROM my table work WHERE my date gt DATE SUB NOW    INTERVAL 1 MONTH   3  TRUNCATE my table work  4  LOCK TABLE my table work WRITE  my table backup WRITE  5  INSERT INTO my table work SELECT   FROM my table backup  6  UNLOCK TABLES  7  RENAME TABLE my table work TO my table  8  DROP TABLE my table backup    This should be completely safe and quite fast   The only problem is that other processes will see table my table disappear for a few seconds   This might lead to errors being displayed in logs everywhere   So it s a safe solution  but it s  noisy    Disclaimer  I am not a MySQL expert  so these solutions might actually be crappy   The only guarantee I can offer is that they work fine for me   If some expert can comment on these solutions  I would be grateful

[sql] Truncate with condition

Examples related to sql

Examples related to mysql

Examples related to truncate