MySQL query to delete rows whose dates are close to another row
I have a table that contains many rows. Rows are guaranteed to be inserted in the order of the named column created_on
, which is the column datetime
. If the specified line has a time created_on
within 5 seconds after the existing line, I would like to delete the specified line.
How do I write a query to delete these lines?
a source to share
SELECT *
FROM TABLE AS A
WHERE EXISTS (
SELECT *
FROM TABLE AS B
WHERE DATE_SUB(A.created_on, INTERVAL 5 SECOND) <= B.created_on
AND B.created_on < A.created_on
)
You understand that this will basically delete all chains of events for 5 seconds one after another, except for the first event in the chain.
Since you cannot pseudonize a table on DELETE, you need to do something like this:
DELETE
FROM so902859
WHERE created_on IN (
SELECT created_on
FROM so902859 AS A
WHERE EXISTS ( SELECT *
FROM so902859 AS B
WHERE DATE_SUB(A.created_on, INTERVAL 5 SECOND) <= B.created_on
AND B.created_on < A.created_on ) )
There are a million ways to skin this cat using JOINs or whatever. I think it is most understandable if it is a little longer.
a source to share