在做mysql嵌套子查询删除时，出现如题错误。mysql语句如下：
delete from student2 where id not in (select id from student2 group by no,name,coursename,courseno,score);
流程梳理一下：
表结构：
mysql> desc student2;
+------------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| no | varchar(7) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
| courseno | varchar(4) | YES | | NULL | |
| coursename | varchar(10) | YES | | NULL | |
| score | int(11) | YES | | NULL | |
+------------+-------------+------+-----+---------+-------+
6 rows in set
mysql>
数据：
mysql> select * from student2;
+----+---------+------+----------+------------+-------+
| id | no | name | courseno | coursename | score |
+----+---------+------+----------+------------+-------+
| 1 | 2015001 | 张三 | 001 | 数学 | 69 |
| 2 | 2015002 | 李四 | 001 | 数学 | 89 |
| 3 | 2015001 | 张三 | 001 | 数学 | 69 |
+----+---------+------+----------+------------+-------+
3 rows in set
操作：删除表中除ID不同，其他信息相同的学生冗余信息。
结果：ERROR 1093 (HY000): You can't specify target table 'student2' for update in FROM clause
原因：在mysql中，不能通过嵌套子查询来直接删除或者修改记录，需要通过别名来指定嵌套子查询作为一个临时表。
解决办法：给嵌套子查询的结果取一个别名，然后从这个表中再次查询出记录,然后再做删除或者修改操作。
delete from student2 where id not in
(select a.id from
(select id from student2 group by no,name,coursename,courseno,score)
a);
操作结果：
mysql> delete from student2 where id not in (select a.id from (select id from student2 gro
up by no,name,coursename,courseno,score) a);
Query OK, 0 rows affected (0.00 sec)
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
