Find and Delete duplicate records in a table

 Posted by Kamlesh420 on 12/14/2011 | Category: Sql Server Interview questions | Views: 7189 | Points: 40
Answer:

Many times you can face problem of duplicate records in table.So How would you identify and delete duplicate records in a table?
For that Firstly check if table has duplicate records or not using below query.

SELECT [FirstName] FROM tblTest GROUP BY [FirstName] HAVING COUNT(*) > 1

Then Delete duplicate records.
DELETE FROM tblTest WHERE ID NOT IN (SELECT MAX(ID) FROM tblTest GROUP BY [FirstName])


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Swaptwap on: 1/25/2012 | Points: 10
Must have a group by of all columns.. Row will not be duplicate if other column is having diff value. like last name may be diff..

Login to post response