[SQL DELETE문제][LeetCode] Delete Duplicate Emails

[문제]

Delete Duplicate Emails 문제 바로가기

Delete Duplicate Emails - LeetCode

[풀이]

- 서브쿼리 

DELETE 
FROM person
WHERE id NOT IN (
    SELECT sub.min_id
    FROM (
        SELECT email, MIN(id) min_id
        FROM person 
        GROUP BY email
    )sub)

DELETE, UPDATE는 하위 절에서 동일한 테이블 사용 못함. 그래서 sub테이블 만들어줌

 

참고 : https://dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html

 

- JOIN

DELETE p1
FROM person p1
    INNER JOIN person p2 ON p1.email = p2.email
WHERE p1.id > p2.id