MySQL Remove Duplicate Records

Sometimes it may be necessary to delete duplicate rows in MySQL, or remove duplicate records in MySQL. In MySQL. Here is how you can remove duplicate records from a MySQL database.

Here are the steps that you need to follow in order to remove duplicate records from MySQL. Let’s say you have the following table salary(id, amount) with duplicate records in it.

Example:

1. Create Table

#start
create table salary(id int, amount int);
#end

2.Insert Data

#start
insert into salary(id,amount) values(1, 10000),(1,25000),(2,35000),(2,35000);
#end

3.Select Table

#start
select * from salary;
#end

There are three methods in which duplicate records in MySQL can be deleted. Let’s take a look at each method in turn.

1. Remove duplicate records using intermediate table

In the first step, you need to select rows that do not have duplicates from the salary table and insert them into another table. The syntax for this can be found here.

Syntax:

#start
CREATE TABLE [copy_of_source] SELECT DISTINCT [columns] FROM [source_table];
#end

Here is the query we will use to select distinct rows from the salary table so that we can put them into another table.

#start
CREATE TABLE salary_copy SELECT DISTINCT id,amount FROM salary;
#end
Select Table
#start
select * from salary_copy;
#end

By using the above query, we are removing rows that are a complete duplicate, meaning they contain identical values for all columns in the table.