Delete Duplicate Emails

Delete Duplicate Emails

    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)