deleting duplicate records

Question

Hi all Is there some way by which i can delete duplicate record that exist in table.I just want one record and rest of them to be deleted by query .Is this can be done through a query . please suggest

Answer 1

use distinct :

SELECT distinct * FROM Table ....

Answer 2

I think the fastest solution would be:

Create Table duplicate as
Select Distinct *
from your_table

then

Delete from your_table

and at the end copy the data back and delete the created Table:

Insert into your_table
Select * from duplicate 

Drop Table duplicate 

And what's really important, create a Primary Key if you don't have one, because there shouldn't be duplicated rows in a table.

Greets
Auro

By searching you would have found 4-5 questions about this.

Answer 3

It Basically depends on the structure of your table,the number of contraints it has and the number of columns in your primary key if it has any.

You need to find the optmized query which will identify the unique records based on the above constraints you have and then most impotantly you need to consider the time taken to delete those duplicates iwth your query.

So nobody can comment unless you publish the complete structure and some example duplicate data.

Hope this little input helps.

Answer 4

Try this -

Add id column as IDENTITY to your table first -

alter <tablename> add id INT IDENTITY

Then use below query to delete duplicate records -

delete 
FROM <tablename>
WHERE id IN
(
SELECT MAX(id)
FROM <tablename>
GROUP BY <columnnames defining uniqueness>
having count ( * )   > 1
)