2

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

  • you probably want to use the lag() function, or simulate the lag function if you're not on mysql 8.0, explained here: stackoverflow.com/questions/11303532/… then delete rows where the lag-row difference is less than 30 minutes. – James May 17 '18 at 19:12
  • Please sort Your data with select * from mytable order by serialNum, serialCode, id; query and then explain record by record like "Record id=N is [not] deleted because ...". And especially explain why You delete record id=2 and keep id=1 - these records are full duplicates by (serialNum, serialCode, dtdate). – Akina May 18 '18 at 5:25
  • Use a "self-join". – Rick James May 29 '18 at 23:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.