MySQL:无法在 FROM 子句中指定目标表 'list' 进行更新

Question

我正在尝试运行以下查询:

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

此查询会导致上述错误:

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

因此，我从建议我运行以下命令的人那里得到了建议:

START TRANSACTION;
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';

此代码运行 - 问题是它永远不会完成。我不确定这是语句本身的问题还是因为它试图自行处理 400 万条记录，但我昨晚上床 sleep 时让它继续运行，今天早上它还没有完成。另外，只是事后的想法:我确实尝试将其纳入交易中，以防事情变得糟糕，所以放入“开始交易；”在本文开始时。

我的印象是这是一个相当常见的问题 - 但不幸的是，我对 SQL 的了解不够，无法接受别人的查询并调整它以满足我自己的需求。 :p

任何人都可以指导我找到一个允许我更新这些记录的解决方案吗？非常感谢。

Answer 1

这可能有效，因此，继续我上面的评论，这是一个答案:

是的，删除“6”和“5”周围的引号，您不希望 MySQL 在每次比较时将 li 列转换为字符串...并创建索引。

CREATE idx_dn_li ON list(dn, li);

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;

索引将使您的 GROUP BY 更快，我认为其中的 li 列也有帮助(正如我所见，在没有记录的情况下)受到影响，您的查询应该只读取索引，但不要相信我的话;) )