首页 > 文章资讯 > 站长教程 > 建站知识 > 解决You can‘t specify target table ... for update in FROM clause

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

时间：2023年12月04日

来源：网络

编辑：佚名

问题来源：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 得到了答案:
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，就ok了：
delete from Person where (email, id) not in
(select email, min(id) minId from (select * from Person) P group by email)

新闻资讯更多

猜你需要