SQL Notes: Delete Duplicate Emails

196. Delete Duplicate Emails

Problem

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest Id.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input: 
Person table:
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
| 3  | john@example.com |
+----+------------------+
Output: 
+----+------------------+
| Id | Email            |
+----+------------------+
| 1  | john@example.com |
| 2  | bob@example.com  |
+----+------------------+
Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

Solution

Algorithm

MySQL

DELETE from Person where Id  not in
(select id from (select min(Id) as Id 
                            from Person group by Email ) as p)

Note that

DELETE from Person where Id  not in
(select id from (select min(Id) as Id from Person group by Email))

doesn’t work in MySQL. I got a runtime error “Every derived table must have its own alias.” It is because “DELETE” and “SELECT” cannot use “FROM” with the same table. The solution is using a middle table.

Reference

Leetcode

Stackoverflow: “Every derived table must have its own alias.”

MySQL: subquery errors