You can't specify target table 'appinfo' for update in FROM clause

今天在做数据订正的时候，写了一个简单的子查询语句，想要把名字重复的应用删除掉一个，删掉其中id号比较大的那个，具体SQL如下：

delete from appinfo where  id in (select max(id) from appinfo group by app_name having count(app_name )>1)

But，报错了。

失败, 详情: You can’t specify target table ‘appinfo’ for update in FROM clause

原因：

MYSQL5.0仍然有的限制，文档中说： In general, you cannot modify a table and select from the same table in a subquery. For example, this limitation applies to statements of the following forms: DELETE FROM t WHERE … (SELECT … FROM t …);UPDATE t … WHERE col = (SELECT … FROM t …);{INSERT|REPLACE} INTO t (SELECT … FROM t …);

所以，将SQL语句改成下面的内容，执行成功。

delete from appinfo where id in
(
    select a.id from
    (
        select max(id) id from appinfo a  group by app_name HAVING count(app_name)>1

    ) a
)