Delete Duplicate Records – Rows


In this artilce we will see how to delete duplicate records. The table must have identity column, which will be used to identify the duplicate records. Table in example is has ID as Identity Column and Columns which have duplicate data are DuplicateColumn1, DuplicateColumn2 and DuplicateColumn3.

Create table TestTable with columns Id as NUMERIC Identity column and Name as NVARCHAR using below script :

IF OBJECT_ID ('dbo.TestTable') IS NOT NULL
    DROP TABLE dbo.TestTable
GO

CREATE TABLE dbo.TestTable
    (
    Id   NUMERIC (18) IDENTITY NOT NULL,
    Name NVARCHAR (50)
    )
GO

Insert some duplicate values into the table :

INSERT INTO dbo.TestTable (Name)
VALUES ('First')
GO

INSERT INTO dbo.TestTable (Name)
VALUES ('Second')
GO

INSERT INTO dbo.TestTable (Name)
VALUES ('Second')
GO

INSERT INTO dbo.TestTable (Name)
VALUES ('Second')
GO

INSERT INTO dbo.TestTable (Name)
VALUES ('Second')
GO

INSERT INTO dbo.TestTable (Name)
VALUES ('Third')
GO

Check TestTable Data using SQL Select Statement :

Select * From TestTable

Query to find duplicate rows :

SELECT Name, Count(*) TotalCount FROM TestTable
GROUP BY Name
HAVING COUNT(*) > 1
ORDER BY COUNT(*) DESC

Syntax to find duplicate rows except latest one :

Select *
FROM TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NAME)

Query to delete duplicate rows :

Delete
FROM TestTable
WHERE ID NOT IN
(
SELECT MAX(ID)
FROM TestTable
GROUP BY NAME)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s