[LeetCode] DeleteDuplicateEmails, solution report, leetcode

[LeetCode] DeleteDuplicateEmails, solution report, leetcode
Question

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 shocould have the following rows:

Id	Email
1	John@example.com
2	Bob@example.com

#Ideas

The meaning of the question is: Find the repeated Email records from the Person table, only keep the records with the smallest Id, and delete other Repeated Records.

Because the search is performed first and then deleted, we have two steps:

select p1.Id from Person as p1 inner join Person as p2 where p1.Email = p2.Email and p1.ID > p2.ID;

delete from Person where Id in ($sql1);

Although the idea is good, it is wrong to write it like this:

delete from Person where Id in (select p1.Id from Person as p1 inner join Person as p2 where p1.Email = p2.Email and p1.ID > p2.ID);

Error message:

You can't specify target table 'Person' for update in FROM clause

In MYSQL, it is forbidden to specify the target table to be updated in the from clause.

Therefore, you need to use the delete syntax specified by MYSQL:

delete t1 from t1,t2 where t1.id = t2.id;ordelete from t1 using t1,t2 where t1.id = t2.id;

delete t1 from t1 left join t2 on t1.id=t2.id where t2.id is null;

AC SQL

delete p1 from Person as p1 inner join Person as p2 on p1.ID > p2.ID and p1.Email = p2.Email;