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 , 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 table should have the following rows:

+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+

Note:

Your output is the whole table after executing your SQL. Use 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;

Software Engineer. Find me @ www.linkedin.com/in/annamariya-jt

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store