[LeetCode] 196. Delete Duplicate Emails

Posted on | In

Problem

Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

1
2
3
4
5
6
7
8
+----+------------------+
| 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:

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

Note:

Your output is the whole Person table after executing your sql. Use delete statement.

Explanation

  1. We need to use the DELETE statement to solve this problem. We can use two tables Person as p1 and Person as p2, then delete WHERE these two tables have the same email AND the first table’s id greater than the second table. We DELETE the first table.

Solution

1
2
3
4
# Write your MySQL query statement below
DELETE p1
FROM Person as p1, Person as p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id;