Similar Problems
Similar Problems not available
Delete Duplicate Emails - Leetcode Solution
Companies:
LeetCode: Delete Duplicate Emails Leetcode Solution
Difficulty: Easy
Topics: database
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;
Delete Duplicate Emails Solution Code
1We can delete duplicate emails by using a set data structure. We can iterate through the given email list and insert each email into the set. If the email is already in the set, then we know it is a duplicate and we can delete it.
2
3set<string> emailSet; for (string email : emailList) { if (emailSet.find(email) == emailSet.end()) { emailSet.insert(email); } else { // delete duplicate email } }