TSQL: Remove Duplicate Rows from a Table in SQL Server

 –Find the duplicate using GROUP BY and COUNT. If you do not use COUNT it will show including unique and duplicates but count column will have value 1 for unique and more than 1 for duplicate records
SELECT
    name, email, COUNT(*)
FROM
    users
GROUP BY
    name, email
HAVING 
    COUNT(*) > 1

Simply group on the both columns

–Find duplicates using RANK 

–Using RANK and Common Table Expression is the best option

–always check first using the select and where condition what records you are going to delete

select the duplicate record by the following query

with CteDuplicates
AS(
select Name
,Age
,Comments
,ROW_NUMBER() OVER(partition by Name, Age, Comments order by Name, Age, Comments) as rank1
from User
)
select * from CteDuplicates
where rank1>1
order by Name, Age

delete the duplicate record by the following query

with CteDuplicates
AS(
select Name
,Age
,Comments
,ROW_NUMBER() OVER(partition by Name, Age, Comments order by Name, Age, Comments) as rank1
from User
)
delete from CteDuplicates
where rank1>1
order by Name, Age

Introduction

Most of the times, we use primary key or unique key for preventing insertion of duplicate rows in SQL Server. But if we don’t use these keys, then it’s obvious that duplicate rows could be entered by the user. After inserting duplicate rows into table, it becomes a major issue to delete those duplicate rows. In that time, we need to delete those duplicate rows to resolve the issue. So this topic will help us to delete those duplicate rows from the specific table.

Background

I used some basic T-SQL code to accomplish the target. So you don’t need to worry to understand this code.

Problem

Firstly, we will create a table, where we will insert some duplicate rows to understand the topic properly. Create a table called ATTENDANCE by using the following code:

CREATE TABLE [dbo].[ATTENDANCE](
	[EMPLOYEE_ID] [varchar](50) NOT NULL,
	[ATTENDANCE_DATE] [date] NOT NULL
) ON [PRIMARY]

Now insert some data into this table.

INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
			('A001',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
			('A001',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
			('A002',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
			('A002',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
			('A002',CONVERT(DATETIME,'01-01-11',5)) 
INSERT INTO dbo.ATTENDANCE (EMPLOYEE_ID,ATTENDANCE_DATE)VALUES
			('A003',CONVERT(DATETIME,'01-01-11',5))

After inserting the data, check the data of the below table. If we grouped the employee_id andattendance_date, then A001 and A002 become duplicates.

EMPLOYEE_ID ATTENDANCE_DATE
A001 2011-01-01
A001 2011-01-01
A002 2011-01-01
A002 2011-01-01
A002 2011-01-01
A003 2011-01-01

So how can we delete those duplicate data?

Solution

First, insert an identity column in that table by using the following code:

ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)

Now the table data will be like the following table:

EMPLOYEE_ID ATTENDANCE_DATE AUTOID
A001 2011-01-01 1
A001 2011-01-01 2
A002 2011-01-01 3
A002 2011-01-01 4
A002 2011-01-01 5
A003 2011-01-01 6

Check the AUTOID column. Now we will start playing the game with this column.

Now use the following code to find out the duplicate rows that exist in the table.

SELECT * FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
	FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

The above code will give us the following result:

EMPLOYEE_ID ATTENDANCE_DATE AUTOID
A001 2011-01-01 2
A002 2011-01-01 4
A002 2011-01-01 5

Ultimately, these are the duplicate rows which we want to delete to resolve the issue. Use the following code to resolve it.

DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _
	FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE)

Now check the data. No duplicate rows exist in the table.

Is it too complicated?

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