In MySQL we will show how to find and delete duplicate record in MySQL , given below example step by step.
Step : 1 – We will prepare sample data for testing to how to find and delete duplicate data in MYSQL.
Create Database :
CREATE DATABASE educationidol;
Step : 2 – Check Create Table
Before table create we will drop same name table;
DROP TABLE IF EXISTS employee ;
Create Table :
CREATE TABLE employee (id int(11) DEFAULT NULL,name varchar(40) DEFAULT NULL,salary int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Step : 3 – Insert record in employee table
INSERT INTO employee (id, name, salary) VALUES
(1, ‘Mike’, 3000),
(2, ‘Mike’, 4000),
(3, ‘Shane’, 3000),
(4, ‘Biden’, 5000),
(5, ‘Shane’, 3000),
(6, ‘Biden’, 5000),
(7, ‘Shane’, 3000),
(8, ‘Biden’, 5000),
(9, ‘Bravo’, 7000);
Step : 4 – First query returns data from the employee table
SELECT * FROM employee ORDER BY name;
Step : 5 – Below query returns the duplicate record from the employee table
SELECT name, COUNT(name) FROM employee GROUP BY name HAVING COUNT(name) > 1;
Step : 6- Below query returns the delete the duplicate record and keep highest id record from the employee table
We will use inner join to delete duplicate record in employee table .
DELETE t1 FROM employee t1 INNER JOIN employee t2 WHERE t1.id < t2.id AND t1.name = t2.name;
