[리트코드/SQL] 7. Delete Duplicate Emails (196)

👉https://leetcode.com/problems/delete-duplicate-emails/description/?envType=study-plan&id=sql-i

Delete Duplicate Emails - LeetCode

---

🤔 문제 분석

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. 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 p
FROM Person AS p
    JOIN Person AS q ON p.email = q.email
WHERE p.id>q.id AND p.email=q.email

 

✍️ check point

distinct가 아닌 delete문을 사용해야 한다.

일반적인 delete 문 사용하기

DELET 
FROM 테이블명
WHERE 조건

이때 테이블에 저장된 모든 데이터가 삭제되더라도 테이블은 여전히 남아있게 된다.

해당 테이블가지 삭제하고 싶을 때는 DROP TABLE문을 사용해야 한다.

 

[참고]

http://www.tcpschool.com/mysql/mysql_basic_delete

코딩교육 티씨피스쿨