Click here to Skip to main content
11,577,965 members (62,675 online)
Click here to Skip to main content

Remove Duplicate Rows from a Table in SQL Server

, 15 Feb 2011 CPOL 250.9K 40
Rate this:
Please Sign up or sign in to vote.
Easiest way to delete more than one duplicate row from a table in SQL Server

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 and attendance_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?

History

  • 16th February, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Sadeque Sharif
Software Developer Meridian Group
Bangladesh Bangladesh
I am:
Manager (IT) at Meridian Group.

I was:
Assistant Manager (Software Division) at KDS Garment Industries Limited.
Assistant Manager (Software Division) at E-Vision Software Limited.

My blog:
crea8ivecode

My preferred work area:
ASP.NET & SQL SERVER.

My email:
sadeque.sharif@yahoo.com

Follow me:
twitter | facebook | linkedin

You may also be interested in...

Comments and Discussions

 
GeneralThank You Pin
Member 1026850628-Jun-15 10:56
memberMember 1026850628-Jun-15 10:56 
GeneralComment: Good Helpful Pin
UsmanMalik883129-Dec-14 20:18
memberUsmanMalik883129-Dec-14 20:18 
GeneralGood Idea to delete the duplicate data Pin
Member 1098618129-Dec-14 13:31
memberMember 1098618129-Dec-14 13:31 
GeneralMy vote of 4 Pin
ravithejag9-Dec-14 22:15
memberravithejag9-Dec-14 22:15 
QuestionThanks a Lot Pin
Er Ayush Gupta14-May-14 23:00
professionalEr Ayush Gupta14-May-14 23:00 
QuestionFor versions from SQL Server 2005 on just use ROW_NUMBER Pin
MartinSmith10000002-Mar-14 0:27
memberMartinSmith10000002-Mar-14 0:27 
AnswerRe: For versions from SQL Server 2005 on just use ROW_NUMBER Pin
Member 108079769-May-14 18:28
memberMember 108079769-May-14 18:28 
QuestionAnother way to remove duplicates by using the HashBytes() function Pin
ntissier21-Feb-14 3:49
memberntissier21-Feb-14 3:49 
QuestionThanks Pin
e.b_star27-Jan-14 2:07
membere.b_star27-Jan-14 2:07 
Questionthanks! Pin
Daan Tuynman17-Nov-13 22:35
memberDaan Tuynman17-Nov-13 22:35 
GeneralTrue Pin
ksanjay8011-Oct-13 0:41
memberksanjay8011-Oct-13 0:41 
GeneralMy vote of 3 Pin
Mas1115-Sep-13 20:03
memberMas1115-Sep-13 20:03 
GeneralMy vote of 5 Pin
Niranjan N Tantri8-Sep-13 20:17
memberNiranjan N Tantri8-Sep-13 20:17 
GeneralMy vote of 2 Pin
archana naik6-Sep-13 20:19
professionalarchana naik6-Sep-13 20:19 
GeneralRe: My vote of 2 Pin
Sadeque Sharif6-Sep-13 20:28
memberSadeque Sharif6-Sep-13 20:28 
GeneralMy vote of 5 Pin
ChargerIIC29-Jul-13 6:37
memberChargerIIC29-Jul-13 6:37 
SuggestionRemove Duplicate Rows from a Table in SQL Server Pin
shaankundan28-May-13 3:10
professionalshaankundan28-May-13 3:10 
GeneralRe: Remove Duplicate Rows from a Table in SQL Server Pin
LoveJenny6-Jun-13 15:27
memberLoveJenny6-Jun-13 15:27 
GeneralRe: Remove Duplicate Rows from a Table in SQL Server Pin
Sadeque Sharif10-Jun-13 23:19
memberSadeque Sharif10-Jun-13 23:19 
Answerremove duplicate row from oracle Pin
Rakesh Sinha31-Oct-12 7:05
memberRakesh Sinha31-Oct-12 7:05 
GeneralRe: remove duplicate row from oracle Pin
Sadeque Sharif31-Oct-12 18:05
memberSadeque Sharif31-Oct-12 18:05 
GeneralMy vote of 5 Pin
vivek.iter27-Sep-12 23:25
membervivek.iter27-Sep-12 23:25 
GeneralRe: My vote of 5 Pin
Sadeque Sharif14-Oct-12 0:08
memberSadeque Sharif14-Oct-12 0:08 
GeneralMy vote of 5 Pin
Member 792008421-Jul-11 9:38
memberMember 792008421-Jul-11 9:38 
Your answer is really superb
GeneralUse the physical Pin
Member 295683825-Feb-11 1:46
memberMember 295683825-Feb-11 1:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150603.1 | Last Updated 16 Feb 2011
Article Copyright 2011 by Sadeque Sharif
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid