Find and delete duplicate record

How to Find and Delete Duplicate Records from a Table

There are many situations where you need to find out duplicate data from the table. Also, you need to find out the no of time the duplicate data arries in my table

In this article, we are going to discuss the how to retrieve duplicate records matching against two or more columns.

To give a practical example of the queries in this post, here’s some sample data:

Return Duplicate Rows with a COUNT

I have a table that has a column called vchRollNo. Let’s say the table name is t_student. I need to find out the records where the vchRollNo data is the same on more than one record.

In this query, we will find the count those records where vchRollNo fields hold duplicate / triplicate record or more.

Using INNER JOIN

If we want to get the details of those students whose vchRollNo fields have more than one records, we will use an INNER JOIN to select all rows from the student table with same student table.

Using Subquery

We can use subquery to get results also.

 

DELETE Duplicate record.

If you want to delete the duplicate record you can simply use the delete statement with above query to delete the record. However if in the table a huge number of records are available you need to go for proper indexing on the columns.

Rasmi Ranjan

A tech guy who loves to learn code. I'm open source enthusiast and a delicious coffee maker

Leave a Reply

Your email address will not be published. Required fields are marked *