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:

CREATE TABLE IF NOT EXISTS `employee` (
  `emp_id` int(3) NOT NULL AUTO_INCREMENT,
  `emp_name` varchar(100) NOT NULL,
  `emp_salary` int(10) NOT NULL,
  PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=11 ;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`emp_id`, `emp_name`, `emp_salary`) VALUES
(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);

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

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

SELECT emp1.emp_name
FROM employee emp1, employee emp2
WHERE emp1.emp_name = emp2.emp_name
AND emp1.emp_id > emp2.emp_id

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:

DELETE emp1 FROM employee emp1, employee emp2
WHERE emp1.emp_name = emp2.emp_name
AND emp1.emp_id > emp2.emp_id

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.