DELETE
전체 데이터 삭제
DELETE FROM 테이블명특정 행만 삭제
DELETE FROM 테이블명
WHERE 조건DELETE로 중복행 제거하기
SQL 코드카타 122 | LeetCode 196. Delete Duplicate Emails
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| email | varchar |
+-------------+---------+Write a solution to delete all duplicate emails, keeping only one unique email with the smallest id.
For SQL users, please note that you are supposed to write a DELETE statement and not a SELECT one.
For Pandas users, please note that you are supposed to modify Person in place.
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.
The result format is in the following example.
Input:
Person table:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Output:
+----+------------------+
| id | email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+풀이
DELETE p1
FROM Person p1, Person p2
WHERE p1.email = p2.email and p1.id > p2.id;-
FROM Person p1, Person p2
: 암묵적 셀프 조인이다. 하나의 테이블에서 관계성을 찾아야 할 때 사용. WHERE 절에 조인 조건을 작성한다. -
WHERE p1.email = p2.email
: 셀프 조인 조건. email을 기준으로 조인 진행 -
p1.id > p2.id
: 조인된 테이블에서 삭제할 행의 조건. 위 조건을 통해 중복된 이메일 중 아이디가 더 큰 행이 삭제된다.
