插入...选择...计数...重复密钥更新错误 [英] insert ...select ... count ... on duplicate key update error

问题描述

我创建了一个表，用于按资产标签保存工单数量.我有2个字段，asset_tag(这是唯一的)和wo_count.我正在尝试编写一个查询，该查询将插入/更新表中的计数.通过对ON DUPLICATE KEY UPDATE的研究，我想出了这一点，但是却遇到了未知的列错误.

I created a table to hold counts of work orders by asset tag. I have 2 fields, asset_tag (which is unique) and the wo_count. I am trying to write a query that will insert/update the counts in the table. Through research on ON DUPLICATE KEY UPDATE, I have come up with this, but am getting unknown column errors.

INSERT INTO mod_workorder_counts (asset_tag, wo_count) 
    (SELECT t.asset_tag, count(*) AS cnt 
        FROM mod_workorder_data t
        WHERE t.asset_tag IS NOT NULL 
     GROUP BY t.asset_tag)
ON DUPLICATE KEY UPDATE mod_workorder_counts.wo_count = t.cnt

当我运行它时，我得到#1054 - Unknown column 't.cnt' in 'field list'.我不确定如何在更新中使用计数值.

When I run this I get #1054 - Unknown column 't.cnt' in 'field list'. I am not sure how to use the count values in the update.

推荐答案

一旦将其引用为"cnt"，就不再需要"t".部分.您应该将其引用为"cnt".

Once you reference it as "cnt", you no longer need the "t." portion. You should reference it as just "cnt".

这篇关于插入...选择...计数...重复密钥更新错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！