Question
Table name: Person
| Column Name | Type |
|---|---|
| id | int |
| varchar |
Each row of this table contains an email. The emails will not contain uppercase letters.
Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.
For SQL users, please note that you are supposed to write aDELETEstatement and not aSELECTone.
The final order of the table does not matter.
Explaination
Usually, people will simply replace the old table with the new table using SELECT to remove the duplicates.
The tricky part of this question is that you cannot create a new table, as the system only checks the original table Person.
Since we cannot use SELECT, the only way to filter out unwanted rows is to use WHERE or HAVING.
First of all, we can call the same table twice by giving them different name temporarily, so that we can compare between rows,
We need to keep the same email which has smaller id, so p.Id > q_Id, then we also need to nake sure that there are acutally duplicate email, so p.Email=q.Email
Solution
DELETE p from Person p, Person q where p.Id>q.Id AND q.Email=p.Email
More SQL 50 questions: here
Please cite the source for reprints, feel free to verify the sources cited in the article, and point out any errors or lack of clarity of expression. You can comment in the comments section below or email to GreenMeeple@yahoo.com