解决You can‘t specify target table ... for update in FROM clause

落日映苍穹つ 2023-09-25 15:45 56阅读 0赞

问题来源：[LeetCode196. Delete Duplicate Emails][]。 一开始我写的SQL代码是：

delete from Person where (email, id) not in
    (select email, min(id) minId from Person group by email)

结果报错：Runtime Error  
You can’t specify target table ‘Person’ for update in FROM clause

谷歌了一下，从[StackOverflow You can’t specify target table for update in FROM clause][StackOverflow You can_t specify target table for update in FROM clause] 得到了答案:  
The problem is that MySQL, for whatever inane reason, doesn’t allow you to write queries like this:

UPDATE myTable SET myTable.A =
    (
        SELECT ... FROM myTable...
    )

That is, ***if you’re doing an UPDATE/INSERT/DELETE on a table, you can’t reference that table in an inner query.***  
The solution is to replace the instance of myTable in the sub-query with (SELECT \* FROM myTable), like this:

UPDATE myTable SET myTable.A =
    (
        SELECT ... FROM (SELECT * FROM myTable) AS something ...
    )

所以本题，把子查询中的Person改为(select \* from Person) P， 就AC了：

delete from Person where (email, id) not in
    (select email, min(id) minId from (select * from Person) P group by email)

[LeetCode196. Delete Duplicate Emails]: https://leetcode.com/problems/delete-duplicate-emails/description/
[StackOverflow You can_t specify target table for update in FROM clause]: https://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause