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

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

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

  1. delete from Person where (email, id) not in
  2. (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:

  1. UPDATE myTable SET myTable.A =
  2. (
  3. SELECT ... FROM myTable...
  4. )

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:

  1. UPDATE myTable SET myTable.A =
  2. (
  3. SELECT ... FROM (SELECT * FROM myTable) AS something ...
  4. )

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

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

发表评论

表情:
评论列表 (有 0 条评论,154人围观)

还没有评论,来说两句吧...

相关阅读