[SQL] LeetCode: 196. Delete Duplicate Emails

196. Delete Duplicate Emails

 

 

문제 

 

 

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.

After running your script, the answer shown is the Person table. The driver will first compile and run your piece of code and then show the Person table. The final order of the Person table does not matter.

 

 

 

풀이

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 )

 

# 서브쿼리 사용

# DELETE의 WHERE절 : 지울 데이터를 거르는 조건 -> NOT IN 사용

 

 

+) JOIN을 활용한 풀이

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