Removing duplicate records from database

I had to remove some dups from one of the tables in my database. Well, there is a couple of ways to do that. They are: using distinct, using derived table, correlated subqueries and using dymanic sql. Each of these technique has its own pros and cons. Well, I prefer using correlated subqueries over to others bases on the scenario that I have. It is always debatable and based on the actual situation and usage.

 Here is what I did for my table.

CREATE TABLE DuplicateRecords (

 TABLE DuplicateRecords (

Id INT IDENTITY,

INT IDENTITY,

Name VARCHAR(50)

)

Name VARCHAR(50)

)

GO

INSERT DuplicateRecords (Name)

 DuplicateRecords (Name)

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Atoshi Sahoo' UNION ALL

SELECT 'Atoshi Sahoo' UNION ALL

SELECT 'Jim Boone' UNION ALL

SELECT 'Jim Boone' UNION ALL

SELECT 'Trevor Carnahan' UNION ALL

SELECT 'Trevor Carnahan' UNION ALL

SELECT 'Atoshi Sahoo' UNION ALL

SELECT 'Atoshi Sahoo' UNION ALL

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Chad Justice' UNION ALL

SELECT 'Chad Justice' UNION ALL

SELECT 'Harpo Marx' UNION ALL

SELECT 'Harpo Marx' UNION ALL

SELECT 'Atoshi Sahoo' UNION ALL

SELECT 'Atoshi Sahoo' UNION ALL

SELECT 'Trevor Carnahan' UNION ALL

SELECT 'Trevor Carnahan' UNION ALL

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Chad Justice' UNION ALL

SELECT 'Chad Justice' UNION ALL

SELECT 'Zeppo Marx' UNION ALL

SELECT 'Zeppo Marx' UNION ALL

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Sanjeeb Sarangi' UNION ALL

SELECT 'Vinay Raturi' UNION ALL

SELECT 'Vinay Raturi' UNION ALL

SELECT 'Daniel Neely' UNION ALL

SELECT 'Daniel Neely' UNION ALL

SELECT 'Vinay Raturi'

SELECT 'Vinay Raturi'

SELECT * FROM DuplicateRecords

 * FROM DuplicateRecords

DELETE DuplicateRecords

 DuplicateRecords

WHERE Id > (

 Id > (

SELECT MIN(Id)

SELECT MIN(Id)

FROM DuplicateRecords dups

WHERE dups.Name = DuplicateRecords.Name

FROM DuplicateRecords dups

WHERE dups.Name = DuplicateRecords.Name

WHERE dups.Name = DuplicateRecords.Name

)

SELECT * FROM DuplicateRecords

 * FROM DuplicateRecords

DROP TABLE DuplicateRecords

 TABLE DuplicateRecords
Published Thursday, September 14, 2006 4:46 PM by sanjeebsarangi
Filed under:

Comments

Tuesday, December 21, 2010 12:49 PM by Saran

# re: Removing duplicate records from database

Nice....I hav a small doubt,how to delete temp files in ms-acces,if i set it as back end

Friday, September 23, 2011 12:17 PM by Ravi

# re: Removing duplicate records from database

This link describes different methods of deleting duplicate records

www.besttechtools.com/SQLArticles.aspx

Leave a Comment

(required) 
(required) 
(optional)
(required)