Delete Subquery SQL

 

아래는 출제 문제

https://leetcode.com/problems/delete-duplicate-emails/description/

Delete Duplicate Emails - LeetCode

 

 

<< 풀이 >>

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
)

 

1. person 테이블에서 중복된 이메일이 있는 행을 삭제

2. 삭제시 id 값이 적은값을 남겨놓을것

 

풀이방법

1. 중복제거한 이메일, id 추출 (이때 id는 적은값 남김)

    select Email, MIN(id) AS min_id
    from person
    group by email

2. 중복제거된 id 값 추출( (이때 id는 적은값 남김)

  select sub.min_id 
  from (
    select Email, MIN(id) AS min_id
    from person
    group by email
  ) sub

3. 중복제거된 id 값을 뺀 나머지행을 삭제

    NOT IN 명령어 사용

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 Join을 사용하는것.

1. 삭제할것만 조회

select *
from person p1
	inner join person p2 on p1.email = p2.email
where p1.id > p2.id

2. 삭제할것만 삭제

delete p1
from person p1
	inner join person p2 on p1.email = p2.email
where p1.id > p2.id