Delete Duplicate Emails — Day 99(SQL)

Photo by Thought Catalog on Unsplash

Today’s question is a SQL question. Let us look into the problem statement.

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

Note:

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

We can try to perform a self-join on our current table on the Email. This statement will give us all the repeated records. After getting the repeated records, we will be filtering out Ids that are not the smallest.

Let us look into the code.

DELETE p1 from Person p1, Person p2 WHERE p1.Email = p2.Email and p1.Id > p2.Id;