mysql delete records same datetime

Question

I have a table with ~ 300.000 records which per pairs have same datetime on a specific column, e.g.

 id    date                  feat1  feat2  
---------------------------------------------------------------------------
 1    10-12-2013 21:35:10    4.2   4.6  
 2    10-12-2013 21:35:10    4.2   4.61  
 3    10-12-2013 21:35:20    4.4   4.3 
 4    10-12-2013 21:35:20    4.4   4.31 
 5    10-12-2013 21:35:30    4.6   4.4 
 6    10-12-2013 21:35:30    4.6   4.41

Obviously the records have identical date values identical per pairs. So i need a mysql query in order to eliminate duplicates and at the end to have only

1     10-12-2013 21:35:10   4.2        4.6
3     10-12-2013 21:35:20   4.4        4.3
5     10-12-2013 21:35:30   4.6        4.4

what is the mysql query which will compare records datetimes and eliminate duplicates?

http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows — M. Abbas, Aug 30 '13 at 10:22
the records are not identical in all columns... but only in date field, so i need a delete criterion based on this specific column — thanili, Aug 30 '13 at 10:33

score 2 · Accepted Answer · answered Aug 30 '13 at 11:22

2

You can an index to delete the duplicates date

ALTER IGNORE TABLE table_name ADD UNIQUE INDEX(date);

This query will delete the duplicates values in your table and after you could drop this index if you want

answered Aug 30 '13 at 11:22

Raspoutine

82
1
7

in fact i am going to keep the unique index on table since i do not need duplicates on datetime. I did not know that adding a unique index deletes duplicates on specific row... – thanili Aug 30 '13 at 11:46

score 1 · Answer 2 · answered Aug 30 '13 at 10:53

Try this:

CREATE TEMPORARY TABLE app
(id int)

INSERT INTO app (id)
SELECT t.id 
FROM myTable t
WHERE EXISTS(
    SELECT 'PREVIOUS'
    FROM myTable t2
    WHERE t2.id < t.id
    AND t2.date = t.date
)

DELETE FROM myTable
WHERE id in (select id from app)

I've used a temporary table because in MySql you can't delete a table where the same table in a subquery. I don't like use DELETE with JOIN (in MySql can be performed).

If you want optimize your query please and combined index on id, date.

Warning: I've considered only date field and id, I excluded the features field (feat1 and feat2). If you want to extend your previous condition at these fields analize their are different from rows.

score -2 · Answer 3 · answered Aug 30 '13 at 10:17

-2

Use the Having clause in the query like this :-

SELECT Count(date_field) as dt_cnt from table name having dt_cnt > 1 ;

answered Aug 30 '13 at 10:17

Gautam Tak

129
1
7

mysql delete records same datetime

3 Answers3