Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
先查找，再删除
delete from Person where (id,Email) not in (select id,email from Person group by Email order by Email);
ERROR 1093 (HY000): You can't specify target table 'Person' for update in FROM clause
意思是：不能在一个表中查询到删除条件，然后又在这个表里删除进行更新。
解决方法：
创造临时表，然后找到删除条件，最后进行删除更新
select * FROM Person p1 INNER JOIN Person p2 WHERE p1.Email = p2.Email AND p1.Id >
p2.Id;
+------+---------+------+---------+
| id | Email | id | Email |
+------+---------+------+---------+
| 3 | a@b.com | 1 | a@b.com |
| 6 | a@b.com | 1 | a@b.com |
| 4 | c@d.com | 2 | c@d.com |
| 6 | a@b.com | 3 | a@b.com |
+------+---------+------+---------+
delete p1 from Person p1 inner join Person p2 where p1.email = p2.email and p1.id > p2.id;
或者
delete p1 from p1 using Person p1 inner join Person p2 where p1.email = p2.email and p1.id > p2.id;
[mysql delete多表关联删除](http://blog.csdn.net/havedream_one/article/details/45421017)