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
See Question&Answers more detail:osIs 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
See Question&Answers more detail:osTry 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.