MySQL Forums
Forum List  »  Newbie

Delete rows within a given time interval
Posted by: Mona S
Date: May 17, 2018 12:27PM

I have created a table with the following values:

create table MyTable (id int, dtdate datetime, serialNum int, serialCode int, status int);

insert into MyTable values

(1, 2012-12-20 01:01:00, 1, 10, 3),
(2, 2012-12-20 01:01:00, 1, 10, 4),
(3, 2012-12-20 01:05:00, 2, 11, 3),
(4, 2012-12-20 01:08:00, 2, 13, 4),
(5, 2012-12-20 01:13:00, 1, 10, 6),
(6, 2012-12-20 01:38:00, 1, 10, 3),
(7, 2012-12-20 01:39:00, 2, 11, 3),
(8, 2012-12-20 01:44:00, 1, 10, 3),
(9, 2012-12-20 01:52:00, 1, 10, 3),
(10, 2012-12-20 01:58:00, 2, 20, 4);

I would like to delete rows from the table that have the same serialNum and serialCode within an interval of 30 minutes and the same to be repeated again with the rows that falls within the next 30 minutes. So in the end, I would have the following result:

(1, 2012-12-20 01:01:00, 1, 10, 3),
(3, 2012-12-20 01:05:00, 2, 11, 3),
(6, 2012-12-20 01:38:00, 1, 10, 3),
(7, 2012-12-20 01:39:00, 2, 11, 3),
(10, 2012-12-20 01:58:00, 2, 20, 4);

I have the code below that only deletes the consecutive duplicate rows. I am not sure how I can incorporate datetime with where clause.

delete from MyTable_duplicate where nSequence in (
select nSequence from MyTable m1
where 0 < (
select count(*)
from MyTable m2
where m2.id = m1.id - 1
and m2.serialNum = m1.serialNum
and m2.serialCode = m1.serialCode)
GROUP BY UNIX_TIMESTAMP(dtdate) DIV 1800 order by id);

Here, MyTable_duplicate is a copy of MyTable. Any help/idea would be highly appreciated!

Thanks

Options: ReplyQuote


Subject
Written By
Posted
Delete rows within a given time interval
May 17, 2018 12:27PM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.