SQL implementation of LeetCode (196. Delete duplicate mailboxes)

[LeetCode] 196.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] |
+----+------------------+

This question asks us to delete duplicate mailboxes. We can first find all the non-duplicate mailboxes, then take the inverse of the number to get the duplicate mailboxes, and delete them all. So how do we find all the non-duplicate mailboxes? We can group them by mailbox, then use the Min keyword to pick out the smaller ones, and then take the complement set to delete them:

Solution 1:

DELETE FROM Person WHERE Id NOT IN
(SELECT Id FROM (SELECT MIN(Id) Id FROM Person GROUP BY Email) p);

We can also use internal intersection to associate the two tables with email addresses, and then delete the same email address with a larger ID. See the code below:

Solution 2:

DELETE p2 FROM Person p1 JOIN Person p2 
ON p2.Email = p1.Email WHERE p2.Id > p1.Id;

We can also use where to directly associate the two tables instead of Join:

Solution 3:

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

Similar topics:

Duplicate Emails

References:

https://leetcode.com/discuss/61176/simple-solution-using-a-self-join

https://leetcode.com/discuss/48403/my-answer-delete-duplicate-emails-with-double-nested-query

This is the end of this article about SQL implementation of LeetCode (196. Delete duplicate mailboxes). For more relevant SQL implementation of deleting duplicate mailboxes, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!