sql语句：mysql 的delete from 子查询限制

1.使用mysql进行delete from操作时，若子查询的 FROM 字句和更新/删除对象使用同一张表，会出现错误。

DELETE FROM tab1 WHERE col1 = ( SELECT MAX( col1 ) FROM tab1 );ERROR 1093 (HY000): You can’t specify target table ‘tab1′ for update in FROM clause

解决方案：针对“同一张表”这个限制，撇开效率不谈，多数情况下都可以通过多加一层select 别名表来变通解决。

DELETE FROM tab1 WHERE col1 = (SELECT MAX( col1 ) FROM ( SELECT * FROM tab1 ) AS t );

2.mysql delete from where in 时后面 的查询语句里不能加where条件

DELETE FROM tab1 WHERE col1 in ( SELECT col1 FROM tab1 where col1 = 1 );

解决方案：综合起来就是mysql delete from where in 时后面 的查询语句里不能加where条件,解决方案是在子查询语句中再包一层select。

DELETE FROM tab1 WHERE col1 in ( SELECT a.col1 FROM ( SELECT col1 FROM tab1 where col1 = 1 ) a);

3. delete from table... 这其中table不能使用别名

delete from tb1 a where a.id in (1,2)

这样是执行失败的。