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

描述

删除重复的 Email，保留 Id 最小的

代码

1
2
3
-- 方法一

delete a from Person a, Person b where a.Email = b.Email and a.Id > b.Id
1
2
3
4
5
6
7
8
9
10
-- 方法二
-- You can't specify target table 'Person' for update in FROM clause
-- 由于 select 和 update 冲突，所以通过创建 tmp 临时表来解决冲突

delete from Person where Id not in (
	select tmp.Id from 
	(
		select min(Id) as Id from Person group by Email
	) tmp
)