LeetCode:196. Delete Duplicate Emails

RojerChen.2018.08.31

今天在解 LeetCode 196. Delete Duplicate Emails 這題,剛好遇到了問題紀錄一下,以下是這題的題目。

Write a 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  |
+----+------------------+
Note:
Your output is the whole Person table after executing your sql. Use delete statement.

針對這個題目,原本我的答案是這樣寫的
delete from Person
where id not in ( select min(id) as id from Person group by email )

只是系統卻回報這樣的錯誤給我 You can't specify target table 'Person' for update in FROM clause,令我困擾的是,這樣的語法在 SQL Server 是正常的,但是在 MySQL 卻是有問題!
根據 MySQL 官方的說法如下 (參考連結)
You cannot update a table and select from the same table in a subquery.

22.6.4 Partitioning and Locking

For storage engines such as MyISAM that actually execute table-level locks when executing DML or DDL statements, such a statement in older versions of MySQL (5.6.5 and earlier) that affected a partitioned table imposed a lock on the table as a whole; that is, all partitions were locked until the statement was finished. In MySQL 5.7, partition lock pruningeliminates unneeded locks in many cases, and most statements reading from or updating a partitioned MyISAM table cause only the effected partitions to be locked. For example, a SELECT from a partitioned MyISAM table locks only those partitions actually containing rows that satisfy the SELECT statement's WHERE condition are locked.
所以遇到這樣的問題,語法改成這樣就可以了
delete from Person
where id not in ( 
    select id from (select min(id) as id from Person group by email) as table1 
)

    Blogger Comment

0 意見: