You can't specify target table 'xxx' for update in FROM clause

1、执行sql语句报上面的错误:

1 DELETE 
2 FROM
3     db_student 
4 WHERE
5     RowGuid IN ( SELECT RowGuid FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) 
6     AND ID NOT IN ( SELECT MAX( ID ) AS id FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 )

报错如下所示 You can't specify target table 'xxx' for update in FROM clause。

原因:因为在MYSQL里,不能先select一个表的记录,在按此条件进行更新和删除同一个表的记录。

详细参考:https://blog.csdn.net/h996666/article/details/81699255

 1 SELECT *
 2 FROM
 3     db_student 
 4 WHERE
 5     RowGuid IN (
 6 SELECT
 7     aa.RowGuid 
 8 FROM
 9     ( SELECT RowGuid FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) aa 
10     ) 
11     AND ID NOT IN (
12 SELECT
13     t.id 
14 FROM
15     ( SELECT MAX( ID ) as id FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) t 
16     )
17     
18 
19 DELETE 
20 FROM
21     db_student 
22 WHERE
23     RowGuid IN (
24 SELECT
25     aa.RowGuid 
26 FROM
27     ( SELECT RowGuid FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) aa 
28     ) 
29     AND ID NOT IN (
30 SELECT
31     t.id 
32 FROM
33     ( SELECT MAX( ID ) as id FROM db_student WHERE age = 22 GROUP BY RowGuid HAVING count( * ) > 1 ) t 
34     )

待续......

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

扫码关注云+社区

领取腾讯云代金券