Click here to Skip to main content
Click here to Skip to main content
Go to top

Remove Duplicate Rows from a Table in SQL Server

, 15 Feb 2011
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:
Deputy 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
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
QuestionThanks a Lot PinprofessionalEr Ayush Gupta14-May-14 23:00 
QuestionFor versions from SQL Server 2005 on just use ROW_NUMBER PinmemberMartinSmith10000002-Mar-14 0:27 
AnswerRe: For versions from SQL Server 2005 on just use ROW_NUMBER PinmemberMember 108079769-May-14 18:28 
QuestionAnother way to remove duplicates by using the HashBytes() function Pinmemberntissier21-Feb-14 3:49 
QuestionThanks Pinmembere.b_star27-Jan-14 2:07 
Questionthanks! PinmemberDaan Tuynman17-Nov-13 22:35 
GeneralTrue Pinmemberksanjay8011-Oct-13 0:41 
GeneralMy vote of 3 PinmemberMas1115-Sep-13 20:03 
GeneralMy vote of 5 PinmemberNiranjan N Tantri8-Sep-13 20:17 
GeneralMy vote of 2 Pinprofessionalarchana naik6-Sep-13 20:19 
GeneralRe: My vote of 2 PinmemberSadeque Sharif6-Sep-13 20:28 
GeneralMy vote of 5 PinmemberChargerIIC29-Jul-13 6:37 
SuggestionRemove Duplicate Rows from a Table in SQL Server Pinprofessionalshaankundan28-May-13 3:10 
GeneralRe: Remove Duplicate Rows from a Table in SQL Server PinmemberLoveJenny6-Jun-13 15:27 
GeneralRe: Remove Duplicate Rows from a Table in SQL Server PinmemberSadeque Sharif10-Jun-13 23:19 
Answerremove duplicate row from oracle PinmemberRakesh Sinha31-Oct-12 7:05 
GeneralRe: remove duplicate row from oracle PinmemberSadeque Sharif31-Oct-12 18:05 
GeneralMy vote of 5 Pinmembervivek.iter27-Sep-12 23:25 
GeneralRe: My vote of 5 PinmemberSadeque Sharif14-Oct-12 0:08 
GeneralMy vote of 5 PinmemberMember 792008421-Jul-11 9:38 
GeneralUse the physical PinmemberMember 295683825-Feb-11 1:46 
Generalhelpfull PinmemberPranay Rana24-Feb-11 2:11 
GeneralYet another way Pinmemberfgrimm21-Feb-11 22:24 
GeneralMy vote of 3 PinmemberMoeez Shahid21-Feb-11 22:23 
QuestionOne more attempt to reinvent the wheel? PinmemberVictor Nijegorodov21-Feb-11 22:11 

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 | Mobile
Web04 | 2.8.140916.1 | Last Updated 16 Feb 2011
Article Copyright 2011 by Sadeque Sharif
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid