196. Delete Duplicate Emails
SQL 50 · Advanced String Functions, Regex, Clause (day 7). Decode it with the hints, solve it on LeetCode, then compare with the reference solution in MySQLPostgreSQL.
Your work
Hints
Open one at a time. Try to finish with as few as you can.
1. What does one output row represent?
A DELETE, not a SELECT.
2. Approach
DELETE p1 FROM Person p1 JOIN Person p2 ON p1.email = p2.email AND p1.id > p2.id.DELETE FROM Person p1 USING Person p2 WHERE p1.email = p2.email AND p1.id > p2.id.
3. The trap
Keep the smallest id, delete the larger duplicates.
Reference solution
Tested on MySQL 8.0 and PostgreSQL 16. Switch dialect at the top of the page.
Show the MySQLPostgreSQL solution
DELETE p1 FROM Person p1 JOIN Person p2 ON p1.email = p2.email AND p1.id > p2.id;DELETE FROM Person p1 USING Person p2 WHERE p1.email = p2.email AND p1.id > p2.id;