MySQL4中嵌套子查询做删除操作会出错，例如下面的SQL：
delete from moviesparam
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
原因：所删除表的在子查询中出现，就会出错，当然，此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;
这样就变相的实现了所要实现的功能。
将上面的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();
责任编辑：小草
