在做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)