Delete rows older than 14 days in MySQL

Question

Summary

I need to purge the history of a table from rows that are older than 14 days.

Being no MySQL Expert, my searches lead me to this:

delete
    from SYS_VERROUS_POLICE
    where idModificationPolice not in (
                select distinct idModificationPolice
                    from SYS_VERROUS_POLICE
                    where date(dateHeureModification) 
                            between curdate() and curdate() - interval 14 day
            );

Thrown Exception

But then I'm stuck with this error message:

Error Code: 1093. You can't specify target table 'SYS_VERROUS_POLICE' for update in FROM clause.

What the...

Context

MySQL seems to be operating in safe mode, so I just won't be able to perform a DELETE where matching dates.

In safe-mode, if I try to delete using only the date field, it doesn't comply.

delete  
  from SYS_VERROUS_POLICE     
 where date(dateHeureModification) < curdate() - interval 14 day    

Error Code: 1175. You are using safe update mode and you tried to update a table without a 
WHERE that uses a KEY column
To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect. 
0,00071 sec

Am I missing something?

don't understand why you need to use NOT IN. you should just be able to do select the correct records older then 14 days with..`SELECT * FROM SYS_VERROUS_POLICE WHERE DATE(dateHeureModification) < CURDATE() - INTERVAL 14 DAY` — Raymond Nijland, Nov 13 '17 at 17:03
@RaymondNijland Yes, I can select. But actually, I can't delete records that the select returns. — Will Marcouiller, Nov 13 '17 at 17:24

Bill Karwin · Accepted Answer · 2017-11-13T17:50:23.360

I understand your point about using safe mode. If you try to use UPDATE or DELETE against a non-indexed expression, it complains, because it can't make an estimate of whether you will accidentally delete your whole table.

Using an expression on DATE(dateHeureModification) > ... is naturally unindexed. MySQL can't do an index lookup against the result of a function.

You can use LIMIT in your delete query to make it satisfy the safe-updates mode. MySQL treats it as sufficient protection against accidentally deleting all the rows in the table, if you use LIMIT.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE DATE(dateHeureModification) < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

It's a good idea to run the delete in limited-size batches anyway, so it doesn't create too many locks or add too much to the undo segment.

Just keep doing DELETE in a loop, deleting batches of 1000 rows at a time, and check rows-affected after each batch. Stop the loop when rows-affected reaches 0.

Another idea: I don't think you really need the DATE() function in your WHERE clause. So you might be able to do the DELETE like below, and it will be able to use an index. Besides, it should be faster to the query to check for any rows if you have an index on dateHeureModification.

DELETE
    FROM SYS_VERROUS_POLICE
    WHERE dateHeureModification < (curdate() - INTERVAL 14 DAY)
    LIMIT 1000;

what about turn off the setting? `SET SQL_SAFE_UPDATES = 0;` ?? — Juan Carlos Oropeza, Nov 13 '17 at 19:08
That's up to you. If you have control over global settings, you can do whatever you want—by doing so, you will accept the risk of accidentally deleting all your data. But it's still a good idea to do the delete in batches, for the reasons I described above. And it's still a good idea to make the search indexable, to help performance. — Bill Karwin, Nov 13 '17 at 19:09
This table's purpose is only to help troubleshoot changes on the main entities. This is a history table that is required to be cleaned every day for records older than 14 days. So an accidental delete is not likely to occur, and if it does, we'll investigate. It shouldn't matter much. — Will Marcouiller, Nov 14 '17 at 17:15

score 3 · Answer 2 · answered Nov 13 '17 at 17:03

3

I don't understand why do you complicate it :

delete
    from SYS_VERROUS_POLICE
    where date(dateHeureModification)
                           <(curdate() - interval 14 day);

answered Nov 13 '17 at 17:03

Daniel E.

2,440
1
14
24

Because MySQL is in Safe Mode and I can't delete only with a non-key field. – Will Marcouiller Nov 13 '17 at 17:19

score 0 · Answer 3 · answered Nov 13 '17 at 17:19

0

@Jean Dous is the correct answer. But just to explain what is the problem in your query. You try to check a condition for the same table you are updating and is like creating a circular reference.

Instead you materialize the table as a subquery so you can use it.

delete
from SYS_VERROUS_POLICE
where idModificationPolice not in (
              select distinct T.idModificationPolice
              from (SELECT *
                    FROM SYS_VERROUS_POLICE) as T
              where date(T.dateHeureModification) 
                    between curdate() and curdate() - interval 14 day
         );

answered Nov 13 '17 at 17:19

Juan Carlos Oropeza

47,252
12
78
118

I came up with the same issue, saying that in safe-mode I can't delete using a non-key column. :\ – Will Marcouiller Nov 13 '17 at 19:05
But that is a totally different error than the one you post in the question. Maybe you could check here https://stackoverflow.com/questions/11448068/mysql-error-code-1175-during-update-in-mysql-workbench – Juan Carlos Oropeza Nov 13 '17 at 19:09

Delete rows older than 14 days in MySQL

3 Answers3

Linked