SQL Server Remove Duplicates

SQL Server Remove Duplicates

Problem: Remove duplicate records from a database table.

Solution: We can achieve this using Common Table Expression and ROW_NUMBER function.

Concept: 

For example we have to delete duplicate records from Color Table and need to delete Highlighted duplicate records.

ID Color Name
1 Black
2 Blue
3 Red
4 Black
5 Red

Now arrange the table records according to color name as shown in below table.

RowNumber ID Color Name
1 1 Black
2 4 Black
1 2 Blue
1 3 Red
2 5 Red

Now we have to delete records with Row Number Greater than 1 Highlighted.

Query:

CREATE TABLE #ColorTable
(
    ID INT IDENTITY(1,1),
    ColorName VARCHAR(10)
)
 
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('BLACK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('PINK');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('RED');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
INSERT INTO #ColorTable (ColorName) VALUES('Blue');
INSERT INTO #ColorTable (ColorName) VALUES('Yellow');
 
WITH CommonTableExpression
as
(
SELECT ROW_NUMBER() over(PARTITION BY ColorName ORDER BY ID,ColorName) RowNumber,ID,ColorName  FROM #ColorTable
)
DELETE FROM CommonTableExpression
WHERE RowNumber <> 1
 
SELECT * FROM #ColorTable

 

Your comments, queries and suggestions are welcomed.

0 Comments
Leave A Comment

Please login to post your valuable comments.

Join the newsletter

Get the latest vLemonn news first

share