196. Delete Duplicate Emails

---

Link

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

Example

풀이1

---

• 중복 이메일이 있으면 id가 가장 작은 값 제외하고 모두 삭제
• DELETE 구문 사용

1. 이메일별 최소 id값을 출력하기 위해 GROUP BY 사용

SELECT email, MIN(id)
FROM person
GROUP BY email

2. 위 테이블 결과에서 MIN(id)값만 출력하기 위해 FROM절 서브쿼리 활용

SELECT s.min_id
FROM ( SELECT email, MIN(id) min_id
	   FROM person
	   GROUP BY email ) s

---

📌 HackerRank > Ollivander's Inventory 에서 했던 같은 실수

• 서브쿼리에서 집계함수 사용시 alias 반드시 붙여서 참조
• 서브쿼리 테이블에 테이블명 붙여주기

---

3. 위에서 작성한 이메일별 최소 id에 해당하는 행을 제외하고 삭제하기

• DELETE FROM 사용
• WHERE 컬럼명 NOT IN 조건 사용

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

🌟 풀이2 - SELF JOIN, DELETE 활용

---

🔍 JOIN 되어있는 테이블에서 관련 데이터 모두 삭제하고 싶을 때

t1

id
1
2
3

t2

id	ref
A	1
B	2
C	3

DELETE t1, t2
FROM t1
INNER JOIN t2 ON t1.id = t2.ref
WHERE t1.id = 1;

---

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

My Answer

---

풀이1.

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

풀이2.

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

---

• MySQL DELETE JOIN
  https://www.mysqltutorial.org/mysql-delete-join/