SQL Coding Exercise – 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  | [email protected] |
| 2  | [email protected]  |
| 3  | [email protected] |
+----+------------------+

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  | [email protected] |
| 2  | [email protected]  |

Submit your solution at https://leetcode.com/problems/delete-duplicate-emails/

Solution 1 – No Select

Like selecting from multiple tables, you could specify multiple tables in the ‘delete’ statement:

DELETE `p1`
FROM `Person` as `p1`, `Person` as `p2`
WHERE `p1`.`Email` = `p2`.`Email` AND `p1`.`Id` > `p2`.`Id`

The `p1` and `p2` actually selects the table twice and `p1`.`Id` > `p2`.`Id` specifies the record with smallest `Id` remains.

Solution 2 – Using Nested Select

Intuitively, the first solution would be:

delete from `Person` where `Id` not in (
    (select Min(`Id`) as `Id` from `Person` group by `Email`)
)

However, as some SQL servers do not support this and it gives error like this:

Runtime Error Message:	You can't specify target table 'Person' for update in FROM clause
Last executed input:	{"headers": {"Person": ["Id", "Email"]}, "rows": {"Person": []}}

The workaround is to add another wrap on the nested select statement:

delete from `Person` where `Id` not in (
    select `A`.`Id` from (select Min(`Id`) as `Id` from `Person` group by `Email`) as `A`
)

–EOF (The Ultimate Computing & Technology Blog) —