Find and Delete Duplicate Records From SQL Table

Introduction

This article helps you to identify duplicate records in a SQL table and removes the records from that table.

Now first create a table.

CREATE TABLE duptext (id varchar(50),name varchar(50),salary int);

The preceding query returns the query result shown in the following image:

Description of duptext table

Insert values into the duptext table

You can simply use the following query to insert multiple rows in a single SQL query.

INSERT into duptext VALUES (1,'sharad',100),(1,'sharad',100),(1,'sharad',100),(2,'nitin',200),(3,'vinod',300),(4,'rahul',400)

Data of duptext table

Count duplicate records query

Before writing the query to count the total duplicate records in a table, I will first describe how to manage this type of query, so you require the "group by" and "having" clauses and the "count" function, and a "select" statement for retrieving the total number of duplicate records.

The following specifies which clause and which function corresponds to the action to be performed.

• Group by clause: determines how the selected rows are grouped.

• Having clause: determines which groups are included in the final result.

• Count Function: returns the total number of rows in a table.

So, in the following query, we use the "id" column with the group by a clause that determines that the id column is to be grouped, the count function only counts records on the basis of the groped id, and the having clause is included at the final result so it returns the id column's count value whose id exists more than once.

select count(*) as DuplicateRecords from duptext group by id having count(*)>1

Output

Now you have determined the total number of duplicate records. Now let's move on to the deletion of duplicate records. There are many ways to delete duplicate records from a SQL table and I describe one of them here.

To delete duplicate records from a table, use the following procedure.

Step 1

Create a temporary table and copy all the distinct rows into it from the "duptext" table, like:

SELECT Distinct * INTO temptable from duptext

Step 2

Delete all records of the "duptext" table.

delete duptext

Step 3

Now copy all data from the "temptable" into the "duptext" table, as in the following:

INSERT INTO duptext select * from  temptable

Step 4

Drop temptable

drop table temptable

Now you have successfully counted and deleted duplicate records from the SQL table.

Quick View Of all things