delete duplicate rows MYSQL

I finally acquired query statement which delete duplicate rows in a table.



First of all, create this table.

CREATE TABLE `new_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `email` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=0 DEFAULT CHARSET=latin1


Second, insert these values.

insert into new_table (email) values ('aa');
insert into new_table (email) values ('aa');
insert into new_table (email) values ('aa');

insert into new_table (email) values ('bb');
insert into new_table (email) values ('bb);

insert into new_table (email) values ('cc');


Now, let's see data on new_table.

select * from new_table;

1 aa
2 aa
3 aa
4 bb
5 bb
6 cc


select * from new_table as a RIGHT JOIN (SELECT MIN(id) as ID, MIN(email) as email, count(id) as cnt FROM new_table GROUP BY email having cnt > 1) b ON  a.email = b.email and a.id != b.ID

this query returns duplicate rows in new_table and it excludes smallest id of duplicated rows.

2 aa 1 aa 3
3 aa 1 aa 3
5 bb 4 bb 2

we should delete these rows.


DELETE  a FROM new_table a
RIGHT JOIN
(SELECT MIN(id) as ID, MIN(email) as email, count(id) as cnt FROM new_table GROUP BY email having cnt > 1) b
ON  a.email = b.email and a.id != b.ID;


or simply this query works.

delete a from new_table a, new_table b where a.id > b.id and a.emaill = b.emaill;


delete from new_table where id not in (select t.id from (select min(id) as id from new_table group by emaill) t);