今天遇到个sql的问题,在mysql上执行 delete from Task where id in (select id from Task where isdelete=1),然后sql报错
You can't specify target table 'Task' for update in FROM clause, 一开始我很费解,在oracle上一直不就是这么玩的么...后来百度之后,才确认是mysql不支持同表内select再delete的操作,于是就考虑使用一张临时表来作为中间过程存储数据。方法如下
create table tmp as select id as col1 from Task where isdelete=1;
delete from Task where id in (select col1 from tmp);
drop table tmp;