Click here to Skip to main content
14,734,630 members
Articles » Database » Database » SQL Server
Tip/Trick
Posted 2 Jun 2016

Stats

11.5K views
13 bookmarked

Identifying and Deleting Duplicate Records from SQL Server Table

Rate me:
Please Sign up or sign in to vote.
3.89/5 (17 votes)
2 Jun 2016CPOL
Identifying and deleting duplicate records from SQL Server Table

Introduction

This article/code/tip guides the SQL developers to identify, highlight (count) and delete the duplicate rows in a table.

Setting Up the Stage

Let us create a table that will contain our data. In this case, I am creating a variable table.

DECLARE @tblLocation AS TABLE (
    ID INT PRIMARY KEY IDENTITY(1, 1),
    Name VARCHAR(50),
    Location VARCHAR(50)
);

Let us insert some data. Data includes employee name and locations he visited. In this case, let me insert the hypothetical locations I have visited!

INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Islamabad, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Jeddah, Saudi Arabia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Washington DC, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Albuqurque, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Santa Fe, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Albuqurque, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Washington DC, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'New York, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Washington DC, USA');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Ankara, Turkey');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Istanbul, Turkey');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Ankara, Turkey');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Moscow, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Saint Petersburg, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Moscow, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Saint Petersburg, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Moscow, Russia');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Frankfurt, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dusseldorf, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Essen, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dusseldorf, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Wuppertal, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dusseldorf, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Frankfurt, Germany');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Sharjah, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Sharjah, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Sharjah, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Aqeel', 'Lahore, Pakistan');

To give a better understanding of the data, let us add some more data. In this case, say the locations visited by my friend Shariq.

INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Multan, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Abu Dhabi, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Karachi, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Rawalpindi, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Islamabad, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Macau, China');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Harbin, China');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Macau, China');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Dubai, UAE');
INSERT INTO @tblLocation (Name, Location) VALUES ('Shariq', 'Lahore, Pakistan');

Let us check what we have? To do so, we execute a simple select statement to fetch the data.

SELECT    *
FROM    @tblLocation;

This statement will return 56 rows.

Let us also check how many times an employee has visited the locations. To fetch the data, we will execute the following select statement:

SELECT      Name, Location, COUNT(*)
FROM        @tblLocation
GROUP BY    Name, Location
ORDER BY    Name, COUNT(*) DESC;

The result is as follows:

What We Want?

Well in my case, I wanted to export the list of locations in a new table. However, in this case, we will try to delete the duplicate records.

First, we need to identify the records we want to delete. For this, we are going to use the ROW_NUMBER function.

SELECT    ID, Name, Location, ROW_NUMBER() OVER (ORDER BY Name, Location) Occurance
FROM      @tblLocation
ORDER BY  Name, Location;;

This select statement will give us the complete data with an incrementing number in the last column named Occurance.

Now we apply the PARTITION BY clause to partition the Occurance column data on the bases of Name and Location. The query will be like this:

SELECT    ID, Name, Location, ROW_NUMBER() OVER (PARTITION BY Name, _
          Location ORDER BY Name, Location) Occurance
FROM      @tblLocation
ORDER BY  Name, Location;

The result of this statement is as below:

Note that the ROW_NUMBER is partitioned on the basis of each employee visiting different locations. The Green Box highlights that I have visited Abu Dhabi 3 times. And see the Gray Box highlighting my Home Town seven times.

Now just to have unique locations visited by employees, we need to delete all the instances of data where Occurance is greater than 1.

The delete statement will be like this:

DELETE FROM @tblLocation
WHERE    ID IN (    SELECT    tbl.ID
                FROM    (    SELECT    ID,Name, Location, ROW_NUMBER() OVER (PARTITION BY Name, _
                             Location ORDER BY Name, Location) Occurance
                            FROM    @tblLocation
                            ) tbl
                WHERE    tbl.Occurance > 1
                );

After executing the above script, simply select the data using the select statement and we will get the desired result. The result is as follows:

Points of Interest

In this, we learned how to identify the duplicate records, highlight them to count the occurance and finally to delete the duplicate records.

License

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

Share

About the Author

Aqeeel
Software Developer
Pakistan Pakistan
No Biography provided

Comments and Discussions

 
GeneralMy vote of 2 Pin
Advay Pandya10-Jun-16 0:33
MemberAdvay Pandya10-Jun-16 0:33 
GeneralRe: My vote of 2 Pin
eFotografo17-Jun-16 4:41
professionaleFotografo17-Jun-16 4:41 
PraiseNice Article.. Pin
Thiruppathi R8-Jun-16 0:05
professionalThiruppathi R8-Jun-16 0:05 
SuggestionAnother Trick Pin
Alison Cadiente5-Jun-16 17:23
professionalAlison Cadiente5-Jun-16 17:23 
PraiseNice Pin
Elena Osminin2-Jun-16 22:42
MemberElena Osminin2-Jun-16 22:42 

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

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