top button
Flag Notify
    Connect to us
      Facebook Login
      Site Registration Why to Join

Facebook Login
Site Registration

How to delete duplicate rows from a table using a single query?

+2 votes
126 views
How to delete duplicate rows from a table using a single query?
posted Aug 7, 2014 by Karamjeet Singh

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

0 votes

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

answer Aug 10, 2014 by Amritpal Singh
Contact Us
+91 9880187415
sales@queryhome.net
support@queryhome.net
#280, 3rd floor, 5th Main
6th Sector, HSR Layout
Bangalore-560102
Karnataka INDIA.
QUERY HOME
...