从重复的选择中插入更新= count(*)

Question

更新某些语料库字典中频繁项的计数。无法在一个查询中执行。相反，我将计数数据收集到临时表中，然后插入/更新字典。想知道在单个命令中更新countincorpus的正确语法是什么。

当前语法：

INSERT INTO temp_table (name, countInCorpus)
    SELECT name,count(*) AS theCount
    FROM corpus
    GROUP BY name having theCount > 9);
INSERT INTO dict (name, countInCorpus)
    SELECT name, countInCorpus
    FROM temp_table ON DUPLICATE KEY UPDATE dict.countInCorpus=temp_table.countInCorpus;

单步语法失败（导致"未知列"字段列表中的" thecount"）：

INSERT INTO dict (name, countInCorpus) 
    SELECT name,count(*) AS theCount
    FROM corpus
    GROUP BY name having theCount > 9
    ON DUPLICATE KEY UPDATE dict.countInCorpus=theCount;

Answer 1

使用 VALUES()：

INSERT INTO dict (name, countInCorpus) 
    SELECT name,count(*) AS theCount
    FROM corpus
    GROUP BY name havingtheCount > 9
    ON DUPLICATE KEY UPDATE dict.countInCorpus = VALUES(countInCorpus);