196. 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  | [e-mail] || 2  | [e-mail]  || 3  | [e-mail] |+----+------------------+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  | [e-mail] || 2  | [e-mail]  |+----+------------------+

第一次知道DELETE还可以这样用，想法是对的但是很傻的用了SELECT=.=

DELETE p1FROM Person p1, Person p2WHERE p1.Email = p2.Email ANDp1.Id > p2.Id

但是不明白的是 怎么就能知道ID在p1 p2中是有序的呢

EXPLANATION:

• Take the table in the example

Id | Email

1 | [e-mail]

2 | [e-mail]

3 | [e-mail]

• 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 | [e-mail] | 1 | [e-mail]

3 | [e-mail] | 1 | [e-mail]

2 | [e-mail] | 2 | [e-mail]

1 | [e-mail] | 3 | [e-mail]

3 | [e-mail] | 3 | [e-mail]

• 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 | [e-mail] | 1 | [e-mail]

• 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 | [e-mail]

will be deleted

还有一个人的答案，在Sqlite可以运行但是MySQL不可以

DELETE FROM Person    WHERE Id IN    (SELECT P1.Id FROM Person AS P1, Person AS P2      WHERE P1.Id > P2.Id AND P1.Email = P2.Email)

In mysql you must't update a table while using select clause , You can only do that step by step . However ,you can use a middle table as :

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

MySQL Don't allow referring delete target table in sub query, a workaround is use ( select * from Person ) to get a new table.

delete from Person where Id in (select p1.Id from (select * from Person) p1, (select * from Person) p2where p1.Email = p2.Email and p1.Id > p2.Id )

Delete and Distinct are completely different, while delete alters the table, distinct only selects distinct values and doesn't alter table.