Delete Duplicate Emails - Leetcode Solution

Problem Statement:

Given a table named Emails which contains two columns: Id and Email. Write a SQL query to delete all duplicate emails from the Emails table. Note: Duplicate emails must be deleted while keeping only one instance of the email.

Example 1:

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

The above table should return:

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

Explanation:

We can see that the email "john@example.com" is duplicated in Id 1 and 3. Thus, we only keep one instance of the email, which is the one with Id 1, and delete the one with Id 3.

Solution:

One way to solve the problem is to use a temporary table to store the non-duplicate emails and then delete the rows from the original table.

Step 1: Create a temporary table named TempEmails with the same schema as the Emails table:

CREATE TABLE TempEmails (Id INT NOT NULL PRIMARY KEY, Email VARCHAR(255) NOT NULL);

Step 2: Insert the non-duplicate emails from the Emails table into the TempEmails table using the DISTINCT keyword:

INSERT INTO TempEmails(Id, Email)
SELECT DISTINCT Id, Email
FROM Emails;

Step 3: Delete all rows from the Emails table:

DELETE FROM Emails;

Step 4: Insert the non-duplicate emails from the TempEmails table back into the Emails table:

INSERT INTO Emails(Id, Email)
SELECT Id, Email
FROM TempEmails;

Step 5: Drop the TempEmails table:

DROP TABLE TempEmails;

The complete SQL query would look like:

CREATE TABLE TempEmails (Id INT NOT NULL PRIMARY KEY, Email VARCHAR(255) NOT NULL);

INSERT INTO TempEmails(Id, Email)
SELECT DISTINCT Id, Email
FROM Emails;

DELETE FROM Emails;

INSERT INTO Emails(Id, Email)
SELECT Id, Email
FROM TempEmails;

DROP TABLE TempEmails;

1