表数据如下:
class=1下的数据:
class=2下的数据:
class=1独有的数据:
select * from tb_student_1 WHERE class=1 and student_name not in(select student_name from tb_student_1 WHERE class=2)
现在想从表中将class为1独有的数据删掉,如果直接使用上面的查询语句嵌套在delete语句中:
DELETE from tb_student_1 where student_name in(select student_name from tb_student_1 WHERE class=1 and student_name not in(select student_name from tb_student_1 WHERE class=2))
此时mysql会报如下错误:
[Err] 1093 - You can't specify target table 'tb_student_1' for update in FROM clause
也就是不能先从同一张表中查出数据,再在同一张表中进行update操作
解决办法:
创建临时表
create table tmp as (select student_name from tb_student_1 WHERE class=1 and student_name not in(select student_name from tb_student_1 WHERE class=2))
#从临时表中查找数据并从student表中删除
DELETE from tb_student_1 where student_name in(select student_name from tmp)
#将临时表删除
drop table if exists tmp
参考:
mysql中You can't specify target table for update in FROM clause错误