天道不一定酬所有勤
但是,天道只酬勤
Hollis出品的全套Java面试宝典不来了解一下吗?

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

Hollis出品的全套Java面试宝典不来了解一下吗?

今天在做数据订正的时候,写了一个简单的子查询语句,想要把名字重复的应用删除掉一个,删掉其中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
)
赞(0)
如未加特殊说明,此网站文章均为原创,转载必须注明出处。HollisChuang's Blog » You can't specify target table 'appinfo' for update in FROM clause
Hollis出品的全套Java面试宝典不来了解一下吗?

评论 抢沙发

HollisChuang's Blog

联系我关于我