Click here to Skip to main content
Click here to Skip to main content

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

, 5 Apr 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
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:

           --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)

Share

About the Author

Tarek Najem
Software Developer
Syrian Arab Republic Syrian Arab Republic
No Biography provided

Comments and Discussions

 
GeneralUse CTE PinmemberMember 23217965-Apr-09 6:51 
You can use CTE and avoid temp tables. An example from a script I recently wrote at work:
WITH    myCte ( RowNo, LineItemId )
          AS ( SELECT   ROW_NUMBER() OVER ( PARTITION BY L.TransferId,
                                            L.ItemGUID ORDER BY L.LineItemId ) AS RowNo,
                        L.LineItemId
               FROM     dbo.IBTLineItems L
             )
    DELETE  FROM dbo.IBTLineItems
    WHERE   LineItemId IN ( SELECT  LineItemId
                            FROM    myCte
                            WHERE   RowNo <> 1 )

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.141015.1 | Last Updated 5 Apr 2009
Article Copyright 2009 by Tarek Najem
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid