Leetsql 196: Delete Duplicate Emails

Posted byczxttkl

196. Delete Duplicate Emails

  • Total Accepted: 18532
  • Total Submissions: 97924
  • Difficulty: Easy
  • Contributors: Admin

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  |
+----+------------------+

Code

DELETE p1
FROM Person p1, Person p2
WHERE p1.Email = p2.Email AND
p1.Id > p2.Id
 

Idea

You have to delete in place. You can’t use “select … ” to return a new table.

 

Also see a similar question: https//nb4799.neu.edu/wordpress/?p=2407