解决You can‘t specify target table ... for update in FROM clause
问题来源：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， 就AC了：
delete from Person where (email, id) not in(select email, min(id) minId from (select * from Person) P group by email)
àì夳堔傛蜴生んèń
矫情吗；*
分手后的思念是犯贱
还没有评论，来说两句吧...