MySQL:您无法在FROM子句中指定要更新的目标表 [英] MySQL: You can't specify target table for update in FROM clause

问题描述

当我尝试运行下面的查询时，我得到您无法在FROM子句中指定目标表'list'进行更新"

When I try to run the query below, I get "You can't specify target table 'list' for update in FROM clause"

我知道实际上还有很多其他类似问题的答复，但是我对SQL的掌握并不强，无法从其他人的解决方案中重建它.

I know there are actually quite a few other replies about similar issues, but my grasp on SQL isn't so strong as to be able to reconstruct it from someone elses solution.

update list
set li = '6'
where li = '5
   and dn in ( SELECT dn FROM list GROUP BY dn HAVING COUNT(*) < 2000 )

推荐答案

您收到此错误吗?

您无法在FROM子句中指定要更新的目标表列表"

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

这是因为如果在另一个subselect语句上使用表，则无法直接更新表.一种替代方法是将表与自身连接.

It's because you cannot update the table directly if you are using it on another subselect statement. One alternative of this is to join the table with itself.

UPDATE  list a
        INNER JOIN
        (
            SELECT  dn 
            FROM    list 
            GROUP   BY dn 
            HAVING  COUNT(*) < 2000 
        ) b ON a.dn = b.dn
SET     a.li = '6'
WHERE   a.li = '5

这篇关于MySQL:您无法在FROM子句中指定要更新的目标表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！