CODEWITHSUNDEEP
mysql
thanili
thanili

Reputation: 617

mysql delete records same datetime

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?

Upvotes: 0

Views: 689

Answers (3)

Raspoutine
Raspoutine

Reputation: 82

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

Upvotes: 2

Joe Taras
Joe Taras

Reputation: 15379

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.

Upvotes: 1

Gautam Tak
Gautam Tak

Reputation: 129

Use the Having clause in the query like this :-

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

Upvotes: -2

Related Questions