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;

--

--

--

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

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

ARTH — Task 18 👨🏻‍💻

Top Programming Languages To Learn in 2019.

What is JAMstack?

Animations and the Anatomy of Keyframes

bouncing arrow animation from example code

DevOps at Microsoft: Innovating on open source

Mars Ecosystem Weekly Report #28

Information Architecture — e$aver app

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
Annamariya Tharayil

Annamariya Tharayil

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

More from Medium

Common SQL Queries: ORDER BY, LIMIT, BETWEEN, NULL, COUNT, GROUP BY

Introduction to SQL

Fig : Relational Database Design (source : researchgate.net)

What is an index in SQL?

Window Functions