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

iTerm2 + zsh + oh-my-zsh The Most Power Full Terminal on macOS

Increase/Decrease the size of the Hadoop cluster on the fly using LVM

Modern Software Development Practices

Injectables vs. Newables

Is CloudSQL for MySQL for you?

Building a desktop app using GTK and Python to display motivational quotes — Part#1

Why your REST services aren’t restful

The Lazy Coder Series: What’s Still Broken in Mobile Dev in 2021?

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

Database Sharding

How do you find the larger of 2 dates in Google Sheets?

SQL IS EASY

What Is Docker & Why It Is Used