mysql - SQL checking duplicates in one column and deleting another -

i need delete around 300,000 duplicates in database. want check card_id column duplicates, check duplicate timestamps. delete 1 copy , keep one. example:

| card_id | time |     | 1234    | 5:30 |      | 1234    | 5:45 |     | 1234    | 5:30 |     | 1234    | 5:45 | 

so remaining data be:

| card_id | time |      | 1234    | 5:30 |      | 1234    | 5:45 | 

i have tried several different delete statements, , merging new table no luck.

update: got working!

alright after many failures got work db2.

delete from( select card_id, time, row_number() on (partition card_id, time)  rn card_table) rn > 1 

rn increments when there duplicates card_id , time. duplicated, or second rn, deleted.

i suggest take approach:

create temporary table tokeep     select distinct card_id, time     t;  truncate table t;  insert t(card_id, time)     select *     tokeep; 

that is, store data want. truncate table, , regenerate it. truncating table, keep triggers , permissions , other things linked table.

this approach should faster deleting many, many duplicates.

if going that, ought insert proper id well:

create temporary table tokeep     select distinct card_id, time     t;  truncate table t;  alter table t add column id int auto_increment;  insert t(card_id, time)     select *     tokeep;