MySQL4中嵌套子查询做删除操作会出错，例如下面的SQL：
delete
from moviesparam
where mpeg in ( select mp.mpeg
from movies mv, moviesparam mp
where mv.mpeg = mp.mpeg)
where mpeg in ( select mp.mpeg
from movies mv, moviesparam mp
where mv.mpeg = mp.mpeg)
执行提示：
错误码: 1093
You can't specify target table 'moviesparam' for update in FROM clause
You can't specify target table 'moviesparam' for update in FROM clause
原因：所删除表的在子查询中出现，就会出错，当然，此SQL放到MySQL5中执行是没有问题的。
为了解决此问题，可以建立一张临时表，将子查询的数据写入到临时表，然后做子查询删除即可。
当然，此处的临时表并被真是真正意义的临时表，是临时创建一个表，用完后再删除。代码如下：
drop
table
if
exists tmp_del_mp;
create table tmp_del_mp as
select mpeg from moviesparam where mpeg not in ( select mpeg from movies);
delete from moviesparam where mpeg in ( select mpeg from tmp_del_mp);
drop table if exists tmp_del_mp;
create table tmp_del_mp as
select mpeg from moviesparam where mpeg not in ( select mpeg from movies);
delete from moviesparam where mpeg in ( select mpeg from tmp_del_mp);
drop table if exists tmp_del_mp;
这样就变相的实现了所要实现的功能。
将上面的SQL放置到JDBC代码中执行：
Statement stmt = conn.createStatement();
stmt.addBatch( "drop table if exists tmp_del_mp");
stmt.addBatch( "create table tmp_del_mp as\n" +
"select mpeg from moviesparam where mpeg not in (select mpeg from movies)");
stmt.addBatch( "delete from moviesparam where mpeg in (select mpeg from tmp_del_mp)");
stmt.addBatch( "drop table if exists tmp_del_mp");
stmt.executeBatch();
stmt.addBatch( "drop table if exists tmp_del_mp");
stmt.addBatch( "create table tmp_del_mp as\n" +
"select mpeg from moviesparam where mpeg not in (select mpeg from movies)");
stmt.addBatch( "delete from moviesparam where mpeg in (select mpeg from tmp_del_mp)");
stmt.addBatch( "drop table if exists tmp_del_mp");
stmt.executeBatch();
本文转自 leizhimin 51CTO博客，原文链接：http://blog.51cto.com/lavasoft/202000，如需转载请自行联系原作者
