196. Delete Duplicate Emails (LeetCode)

Delete Duplicate Emails - LeetCode

Write an SQL query to delete all the duplicate emails, keeping only one unique email with the smallest id. Note that you are supposed to write a DELETE statement and not a SELECT one.

---

풀이

제출한 오답

delete from Person 
where (email, id) not in (
select email, min(id) as min_id 
from Person 
group by email)​

-> 서브쿼리를 한 번만 사용할 수는 없을까 해서 where 절에 다중컬럼 서브쿼리를 사용했지만 에러가 났다.

다시 제출한 답

delete from Person 
where id not in 
    (select sub.min_id
     from(
         select email, min(id) as min_id 
         from Person 
         group by email) sub)

2022.09.16