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:
- 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.