LeetCode MySQL-196. Delete Duplicate Emails

    AllenLi
    LeetCode MySQL

    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.

    寫一個 SQL Query來刪除所有重複的 email，只保留 id 最小的 email，且需使用 DELETE 語法，不能使用 SELECT 語法。

    Table: Person

    +-------------+---------+
| Column Name | Type    |
+-------------+---------+
| id          | int     |
| email       | varchar |
+-------------+---------+
id is the primary key column for this table.
Each row of this table contains an email. The emails will not contain uppercase letters.

    Example 1:

    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  |
+----+------------------+
Explanation: john@example.com is repeated two times. We keep the row with the smallest Id = 1.

    Solution:
    1. 刪除 Person 的 table1(tb1)
    2. 來自「Person => tb1」、「Person => tb2」
    3. 取出 email 相同，且 id 較大的資料。

    Code.1:

    DELETE tb1 
  FROM Person AS tb1 
  JOIN Person AS tb2 
    ON tb1.email = tb2.email 
WHERE tb1.id > tb2.id;

    Code.2:

    DELETE tb1
	FROM Person AS tb1, Person AS tb2
WHERE tb1.email = tb2.email AND tb1.id > tb2.id;

    newPerson

    Related Posts