Need to delete duplicate entries in Mysql

[dubya]

I have a database of over 600,000 business listings, and there are probably 100,000 duplicates in there.

So, I'm asking how to delete the duplicates efficiently. I made a table with all of the listing names that have duplicates. I need to somehow check all the names against the 600,000 listings and delete the duplicates.

The duplicate listings have all the same information, just a different ID in the table.

Any help is appreciated,

Thanks,
Dave

 

[Kate]

You could use SELECT DISTINCT (without the ID field)
so you won't get dupes
then INSERT the recordset into a fresh table, same structure as original table, the ID will be autogenerated.

 

[Brujah]

If this query works
SELECT * FROM table1,table1 as vtable
WHERE vtable.id > table1.id
AND vtable.fieldname = table1.fieldname

then this should remove the duplicates
DELETE FROM table1
USING table1,table1 AS vtable
WHERE vtable.id > table1.id AND table1.fieldname = vtable.fieldname

re: Mysql remove duplicate data or rows with DISTINCT

 

[dubya]

Thanks, I've since found a solution to my problem.

 

[timstyris]

Hi..
Thanks for sharing the information...its really good for me... also i have read the solution...