Introduction

Most of times we use primary key or unique key for preventing to insert duplicate rows in SQL Server. But if we don't use this keys then it's obvious that duplicate rows could be entered by the user. After inserting duplicate rows into table, it's become an 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 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 insert the data, check the data of below table. if we grouped the employee_id and attendance_date then A001 and A002 becomes duplicate.

So how can we delete those duplicate data?

Solution

First insert a 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.

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

Now use the following code to find out the duplicate rows exist in 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.

Ultimately this 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 table.

Is it was to complicated?