Posted on 2008-07-07 18:12
追风舞者 阅读(167)
评论(0) 编辑 收藏
有如下的一个表 user(id,name,type) ,dept(id,userid,name)
delete from test where id in (select u.id from user u inner join dept d on u.id=dept.userid where d.name='技术部');
当在MySql下执行这条查询时，数据库报错:You can't specify target table 'test' for update in FROM clause
查阅资料发现：MySQl下不支持这样的同时有select跟delete/update同一个表的操作。
修改如下：
delete from test where id in (select * from (select u.id from user u inner join dept d on u.id=dept.userid where d.name='技术部') ta);
Success!!!