Delete duplicate records from table.

Here I will show  how to delete duplicate records from a table.

Table Data:

DECLARE @TEMPTABLE TABLE (ID INT IDENTITY(1,1), NAME NVARCHAR(100), SALARY DECIMAL(10,2))

 INSERT INTO @TEMPTABLE(NAME,SALARY)

SELECT ‘H’,8000 UNION ALL

SELECT ‘G’,20000 UNION ALL

SELECT ‘A’,18000 UNION ALL

SELECT ‘F’,55000 UNION ALL

SELECT ‘E’,10000 UNION ALL

SELECT ‘H’,25000 UNION ALL

SELECT ‘B’,10000 UNION ALL

SELECT ‘C’,9000 UNION ALL

SELECT ‘D’,15000 UNION ALL

SELECT ‘E’,16000 UNION ALL

SELECT ‘H’,12000 UNION ALL

SELECT ‘A’,20000 UNION ALL

SELECT ‘A’,48000 UNION ALL

SELECT ‘C’,9000 UNION ALL

SELECT ‘B’,10000 UNION ALL

SELECT ‘A’,15000

 

SELECT * FROM @TEMPTABLE ORDER BY NAME

1

In this table we have so many duplicate records and I want to delete duplicate name records.

1st Method:

Logic behind the query: here I want to delete only those records those are not found in sub query. In sub query I am generating unique records based on the name by grouping them and getting max id for each group. Now the final query will delete all the records except the max id of each group based on name field.

DELETE FROM @TEMPTABLE WHERE ID NOT IN (SELECT MAX(ID) FROM @TEMPTABLE GROUP BY NAME)

SELECT * FROM @TEMPTABLE ORDER BY NAME

2

2nd Method:

Logic behind the query: here I want to delete only those records those are not found in sub query. In sub query I am generating unique records based on the name by grouping them and getting min id for each group. Now the final query will delete all the records except the min id of each group based on name field.

DELETE FROM @TEMPTABLE WHERE ID NOT IN (SELECT MIN(ID) FROM @TEMPTABLE GROUP BY NAME)

SELECT * FROM @TEMPTABLE ORDER BY NAME

3

 

3rd Method:

Logic behind the query: In this query I am using the concept of COMMON TABLE EXPRESSION (CTE) and  ROW_NUMBER(). Here “PARTITION BY NAME” will divide whole table into segments based on name, it is same as group by; and assign 1 for unique record else for every duplicate record it increment counter by 1. So the output for query written in CTE block is

5

And then we delete those records those have duplicate counter greater than 1.

;WITH CTE AS

(

SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY NAME) AS DUPLICATES FROM @TEMPTABLE

)

DELETE FROM CTE WHERE DUPLICATES > 1

SELECT * FROM @TEMPTABLE ORDER BY NAME

4

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