sql - deleting duplicate records

Question

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

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
)

Please take into consideration, that it only deletes one duplicate of existing row. If there are i.e. three rows that share grouping condition - this will not work, unless you execute query multiple times (until no rows were deleted) or change the delete condition.