MySQL delete duplicate rows from table using single query

MySQL delete duplicate rows from table using single query:

In this article,I will show how to delete duplicate records from table using single MySQL Query.


Example:

I have a table contain departments (department_table).

CREATE TABLE IF NOT EXISTS `department_table` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `department` VARCHAR(250) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MYISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


Department table contains duplicate records of department.

id     Department
1 Sales
2 R&D
3 Support
4 Account
5 Sales

You can use following query to remove duplicate records from table using single query:

DELETE D2
FROM   department_table D1
JOIN   department_table D2 ON (D2.department = D1.department )  AND( D2.id > D1.id); 

OUTPUT:

id     Department
1 Sales
2 R&D
3 Support
4 Account

I hope this MySQL query will help you to remove duplicate records from table.