how to delete duplicate records in mysql table

Question

I'm having an issue with finding and deleting duplicate records, I have a table with IDs called CallDetailRecordID which I need to scan and delete records, the reason there are duplicates is that I'm exporting data to special arching engine works with MySQL and it doesn't support indexing.

I tried using "Select DISTINCT" but it dosn't work, is there is another way? I'm hoping I can create a store procedure and have it run weekly to perform clean up.

your help is highly appreciated.

Thank you

Why is the DISTINCT not working?

Peter Kiss
– Peter Kiss

2012-03-01 07:05:05 +00:00
Commented Mar 1, 2012 at 7:05 — Peter Kiss
– Peter Kiss, Commented Mar 1, 2012 at 7:05

Matthew Scragg · Accepted Answer · 2012-03-01 07:11:29Z

3

CREATE TABLE tmp_table LIKE table    
INSERT INTO tmp_table (SELECT * FROM table GROUP BY CallDetailRecordID)
RENAME table TO old_table
RENAME tmp_table to table

Drop the old table if you want, add a LOCK TABLES statement at the beginning to avoid lost inserts.

answered Mar 1, 2012 at 7:11

Matthew Scragg

4,6783 gold badges23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

how to delete duplicate records in mysql table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related