Delete duplicate records except one record in MySQL

This kind of questions are asked many times in many Interviews, so better we should know how to answer them.

Let’s create a table first and insert some dummy duplicate records:

Run this above query in a SQL browser and you will get a table like below:

emp_id  emp_name emp_salary
1 Roj 100
2 Fren 50
3 Rin 300
4 Yen 150
5 Roj 500
6 Noop 250
7 Fren 600
8 Rin 120
9 Yen 160
10 Con 590

Before deleting the duplicate records, lets see how to filter out only the duplicate values from the above table, lets write a query:

If you run the above query, you will get only the duplicate records, the query is not so tough, creating alias for the same table and filtering duplicate records by matching the duplicate names (=) and with a (>) condition on emp_id.

Now, to delete these records, instead of SELECT you are going to use DELETE, lets do it:

If you compare the SELECT query and DELETE query, you can see a small change between them, ie., column name (emp1.emp_name) is removed in DELETE query, because we cannot delete a single column using DELETE, and actually we have the entire row, so you have to use only the alias name (emp1) in DELETE query.

Related Posts

Leave a Reply