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
)
0 意見:
張貼留言