MySql 不支持子查询的变通方法


Warning: Undefined array key "HTTP_REFERER" in /www/wwwroot/prod/www.enjoyasp.net/wp-content/plugins/google-highlight/google-hilite.php on line 58
到目前为止,MySQL 还不能支持这样的操作,子查询的 FROM 字句和更新/删除对象不能使用同一张表。

mysql> 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

针对“同一张表”这个限制,撇开效率不谈,多数情况下都可以通过临时表来变通解决,像这样

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