[LeetCode] 196. Delete Duplicate Emails

来源：互联网发布：马克思资本论知乎编辑：程序博客网时间：2024/05/22 06:32

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 NOT IN (SELECT MIN(Id) FROM Person GROUP BY Email);
</pre><pre name="code" class="sql" style="font-family: Menlo, Monaco, Consolas, 'Courier New', monospace; font-size: 13px; line-height: 1.42857;">但是报了这个错误：<pre style="box-sizing: border-box; position: relative; margin-top: 0px; margin-bottom: 10px; border-radius: 4px; border: 1px solid rgb(204, 204, 204); overflow: hidden; font-family: Menlo, Monaco, Consolas, 'Courier New', monospace; font-size: 13px; white-space: pre-wrap; padding: 9.5px; line-height: 1.42857; color: rgb(51, 51, 51); word-break: break-all; word-wrap: break-word; background: rgb(245, 245, 245);">Runtime Error Message:    You can't specify target table 'Person' for update in FROM clause

这个错误的意思是禁止在FROM子句中指定被更新的目标表。且只在MySQL中出现，Oracle都不会存在这种情况。

于是只好用INNER JOIN。

DELETE p1 FROM Person p1 INNER JOIN Person p2 where p1.Email = p2.Email and p1.Id > p2.Id

0 0