Question

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]  |
+----+------------------+

Note:

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

SQL Schema

Truncate table Person
insert into Person (Id, Email) values ('1', '[email protected]')
insert into Person (Id, Email) values ('2', '[email protected]')
insert into Person (Id, Email) values ('3', '[email protected]')

My Interesting Code

delete from Person
where Email in (
    select * 
    from (
        select Email
        from Person
        group by Email having count(Email) > 1
    ) temp1
) and Id not in (
    select *
    from (
        select min(Id)
        from Person
        group by Email having count(Email) > 1
    ) temp2
)

My Perspective

This question is easy, but you must use “delete”. Firstly, you need to find all the duplicated “Emails” and all the minimum “Ids”. Then, you can use “delete” to finish this question.

However, if you do not use “select *”, there will be an error, which is “You can’t specify target table “Person” for update in FROM clause. This is because sql do not allow anyone using itself quering results to update itself. So you need to make the quering result become another table, using “select *”.

Also, there is a useful link, and I think it can help you. (chinese version)