天道不一定酬所有勤
但是,天道只酬勤

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
)
(全文完)
欢迎关注HollisChuang微信公众账号
打赏

如未加特殊说明,此网站文章均为原创,转载必须注明出处。HollisChuang's Blog » You can’t specify target table ‘appinfo’ for update in FROM clause

分享到:更多 ()

HollisChuang's Blog

联系我关于我