Last active
August 10, 2017 14:43
-
-
Save stellamiranda/9b3c511d2228a924686fbd5933e35ed0 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 | | |
+----+------------------+ | |
Answer: | |
DELETE p1 | |
FROM Person p1, Person p2 | |
WHERE p1.Email = p2.Email AND p1.Id > p2.Id |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment