Delete Duplicate Emails SQL Challenge - Oracle Solution

1. Introduction

This blog post addresses a practical problem in Oracle databases – eliminating duplicate email addresses while retaining the one with the smallest id. This operation is crucial for database cleanup and ensuring data uniqueness.

Problem

The challenge is to write an Oracle SQL query that deletes duplicate email entries from the Person table, only keeping the email with the smallest id.

Database Table:

Person Table:

id	email
1	john@example.com
2	bob@example.com
3	john@example.com

3. Oracle Solution

DELETE FROM Person
WHERE id NOT IN (
    SELECT MIN(id)
    FROM Person
    GROUP BY email
);

Output:

id	email
1	john@example.com
2	bob@example.com

Explanation:

1. The query deletes records from the Person table where the id is not the smallest id for a given email.

2. It uses a subquery to select the minimum id for each email (grouped by email).

3. Only those rows whose id matches the minimum id in the subquery are retained.

4. As a result, all duplicate emails are deleted, leaving only one unique email with the smallest id.