Removing Duplicate rows

Hello friends,

I have some problem here.

I have one table in which there are number of duplicate entries. Some have 10 duplicate entries while some have 40 duplicates of it. The Primary key is the Auto-Increment ID.

What I want to do is that remove all the duplicate ones and keep one instance of that entry.

Is there any way that it can be done? I have tried some PHP scripts and all. But the loops and ways I have used are eating up more resources and it never completes.

If there is a way to acomplish this in some less queries then it will be very good.

Thanking You.

 

http://www.google.com/search?num=100&hl=en&lr=&q=SQL+distinct

 

Yes there is. You can create a primary key of the id and something other unique in your entries which will remove the other duplicates (be careful you can only do this once).

 

create table new_table (select distinct(field_with_duplicates),field1,field2,...fieldn from original_table)

This will create a new table without the duplicates. This is much faster than performing deletes, which in any event fragment your table, and also that way no time is wasted on updating any indexes with each delete.
Recreate your indexes afterwards. I haven't found a faster way yet.

 

Thanks a lot

Rep added.

 

Pleasure! Thanks for the rep.