196. Delete Duplicate Emails

来源：互联网发布：linux如何查看路由走向编辑：程序博客网时间：2024/05/17 08:50

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  |+----+------------------+

EXPLANATION:

Take the table in the example

+----+------------------+| Id | Email            |+----+------------------+| 1  | john@example.com || 2  | bob@example.com  || 3  | john@example.com |+----+------------------+

Join the table on itself by the Email and you'll get:

FROM Person p1, Person p2 WHERE p1.Email = p2.Email

p1.Id | p1.Email | p2.Id | p2.Email

1 | john@example.com | 1 | john@example.com

3 | john@example.com | 1 | john@example.com

2 | bob@example.com | 2 | bob@example.com

1 | john@example.com | 3 | john@example.com

3 | john@example.com | 3 | john@example.com

From this results filter the records that have p1.Id>p2.ID, in this case you'll get just one record:

AND p1.Id > p2.Id

p1.Id | p1.Email | p2.Id | p2.Email

3 | john@example.com | 1 | john@example.com

This is the record we need to delete, and by saying

DELETE p1

in this multiple-table syntax, only matching rows from the tables listed before the FROM clause are deleted, in this case just

p1.Id | p1.Email

3 | john@example.com

will be deleted

# Write your MySQL query statement belowDELETE a    FROM Person a ,Person b    WHERE a.Email=b.Email && a.Id>b.Id;

978 ms solution beats 93.46% submissions

# Write your MySQL query statement belowdelete from Person     where Id not in     ( select A.Id         from         (select min(Id) as Id             from Person             GROUP BY Email) A );

注意：

where we try this clause :

delete from Person where id not in(select min(id) as id from Person group by email)

you will be noted " You can't specify target table 'Person' for update in FROM clause ", The solution is using a middle table with select clause:

delete from Person where id not in(     select t.id from (        select min(id) as id from Person group by email    ) t)

0 0