[LeetCode]DeleteDuplicateEmails,解題報告，leetcode

[LeetCode]DeleteDuplicateEmails,解題報告，leetcode
題目

SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

Id	Email
1	john@example.com
2	bob@example.com
3	john@example.com

Id is the primary key column for this table.
For example, after running your query, the above Person table should have the following rows:

Id	Email
1	john@example.com
2	bob@example.com

# 思路

題目的意思是：從Person表中找出Email重複的記錄，只保留Id最小的記錄，刪除其他的重複記錄。

因為是先尋找，再刪除，所以我們也分兩步進行:

select p1.Id from Person as p1 inner join Person as p2 where p1.Email = p2.Email and p1.ID > p2.ID;

delete from Person where Id in ($sql1);

思路雖然是好的，但是這樣寫卻是錯誤的：

delete from Person where Id in (select p1.Id from Person as p1 inner join Person as p2 where p1.Email = p2.Email and p1.ID > p2.ID);

錯誤資訊為:

You can't specify target table 'Person' for update in FROM clause

意思是：在MYSQL中，禁止在from子句中指定被更新的目標表。

所以，需要使用MYSQL規定的delete文法：

delete t1 from t1,t2 where t1.id = t2.id;ordelete from t1 using t1,t2 where t1.id = t2.id;

delete t1 from t1 left join t2 on t1.id=t2.id where t2.id is null;

AC SQL

delete p1 from Person as p1 inner join Person as p2 on p1.ID > p2.ID and p1.Email = p2.Email;