Example:
Suppose you 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
