Delete duplicate emails MySQL

Thanks to this awesome post on GitHub! This is how you can delete duplicate emails in MySQL:

# Time: O(n^2)
# Space: O(n)
#
# 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 | 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 should have the following rows:
#
# +----+------------------+
# | Id | Email |
# +----+------------------+
# | 1 | john@example.com |
# | 2 | bob@example.com |
# +----+------------------+
#

# Write your MySQL query statement below
DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND p1.Id > p2.Id

https://github.com/kamyu104/LeetCode/blob/master/MySQL/delete-duplicate-emails.sql

Leave a Reply

Your email address will not be published. Required fields are marked *