You can't specify target table 'xxx' for update in FROM clause
You can't specify target table 'xxx' for update in FROM clause
别先生
发布于 2019-06-03 11:20:34
发布于 2019-06-03 11:20:34
文章被收录于专栏：别先生
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 )待续......
本文参与 腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。
原始发表：2019-05-12 ，如有侵权请联系 cloudcommunity@tencent.com 删除
评论
登录后参与评论
推荐阅读
腾讯云开发者
