Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

Delete duplicate entries from a data store while leaving a single copy

4.00/5 (1 vote)
5 Apr 2009CPOL 15.5K  
How to delete duplicate entries from a data store, while leaving a single copy.

Introduction

The code explained here will show how to delete duplicate entries from a data store, while leaving a single copy. The code will first create a temp table with duplicated records for the field 'FullName' and then get the IDs of the record which must be deleted and then delete those records.

Using the code

Here is the complete SQL code:

SQL
           --Create Temp Table
IF OBJECT_ID('TempDup') IS NOT NULL
DROP TABLE 'TempDup'
GO
CREATE TABLE [dbo].[TempDup]
(
    [ID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_TempDup_ID] DEFAULT (newid()),
    [FullName] [nchar](10) NOT NULL,
    CONSTRAINT [PK_TempDup] PRIMARY KEY CLUSTERED ( [ID] ASC )ON [PRIMARY]
) ON [PRIMARY]
GO
 INSERT INTO TempDup VALUES   ( NEWID(), 'N1')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N2')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N3')
 INSERT INTO TempDup VALUES   ( NEWID(), 'N3')
-- This code will select the Duplicate row only and keep single copy from row
SELECT [ID], [FullName], [RowIndex]
FROM
(
    SELECT 
        [ID], [FullName], RANK() OVER (PARTITION BY [FullName] 
        ORDER BY [ID] ASC) AS [RowIndex]
    FROM [dbo].[TempDup]
)[T1]
WHERE [T1].[RowIndex] > 1
GO
-- If replace SELECT SQL command with DELETE SQL command
DELETE FROM [dbo].[TempDup] WHERE [ID] IN
(
    SELECT [ID] FROM
    (
        SELECT 
        [ID], [FullName],
         RANK() OVER (PARTITION BY [FullName] ORDER BY [ID] ASC) AS [RowIndex]
        FROM [dbo].[TempDup]
    )[T1] WHERE [T1].[RowIndex] > 1
)
GO

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)