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一个表的记录,在按此条件进行更新和删除同一个表的记录。

详细参考:IT虾米网

 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     )

待续......


发布评论
IT序号网

微信公众号号:IT虾米 (左侧二维码扫一扫)欢迎添加!

sql 语句中count()有条件的时候为什么要加上or null知识解答
你是第一个吃螃蟹的人
发表评论

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。