Need to delete duplicate entries in mysql

dubya · 10-08-2009, 08:55 AM

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

sdsinc · 10-08-2009, 12:47 PM

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 · 10-08-2009, 01:03 PM

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 · 10-09-2009, 08:18 AM

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

timstyris · 11-04-2009, 02:35 AM

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

10-08-2009, 08:55 AM	THREAD STARTER #1 (permalink)
dubya NamePros Regular Join Date: Dec 2007 Posts: 241 Follow @DavidWiles	Need to delete duplicate entries in mysql 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

10-08-2009, 12:47 PM	#2 (permalink)
sdsinc Domains my Dominion Join Date: Aug 2005 Location: Web 1.0 Posts: 9,963	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. __________________ NameNewsletter.com - free lists of available domain names ZoneFiles.net (beta) - ccTLD and gTLD droplists

10-08-2009, 01:03 PM	#3 (permalink)
Brujah Senior Member Join Date: Apr 2006 Posts: 1,478	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 __________________ Free Keyword Popularity, CPC, etc... Namejet, Snapnames, GoDaddy Lists Offers valid for 24 hours. Now Available: Rented.com

11-04-2009, 02:35 AM	#5 (permalink)
timstyris New Member Join Date: Nov 2009 Posts: 3	Hi.. Thanks for sharing the information...its really good for me... also i have read the solution... __________________ dsi r4

10-09-2009, 08:18 AM	THREAD STARTER #4 (permalink)
dubya NamePros Regular Join Date: Dec 2007 Posts: 241 Follow @DavidWiles	Thanks, I've since found a solution to my problem.

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)