您可以在ON DUPLICATE KEY中使用汇总值吗 [英] Can you use aggregate values within ON DUPLICATE KEY

问题描述

我正在尝试编写类似于以下内容的查询:

I am trying to write a query similar to:

INSERT INTO SomeTable(field1, field2) 
SELECT 'blah' AS field1, 
        MAX(AnotherTable.number) AS field2
FROM AnotherTable
ON DUPLICATE KEY 
UPDATE field1= 'blah', field2 = MAX(AnotherTable.number)

我收到错误代码:1111 无效使用组功能.

I get Error Code: 1111 Invalid use of group function.

通读MySql文档:

http://dev.mysql.com/doc/refman/5.1/en/insert-select.html

感兴趣的行是:

"在ON DUPLICATE KEY UPDATE的values部分中，只要您不在SELECT部分​​中使用GROUP BY，就可以引用其他表中的列.其副作用是您必须限定非唯一列值部分中的名称."

这是我遇到的问题吗?我不是在Select语句中专门执行GROUP BY，而是通过使用聚合函数(Max)，然后可能会隐式分组.

Is this the problem I am seeing? I am not specifically doing a GROUP BY in the Select statement, but by using an aggregate function (Max), then I may be grouping implicitly.

如果有人可以肯定地知道我是在暗中做GROUP BY还是有其他方法可以达到期望的结果，我将非常感激.

If anyone knows for sure if I am implicitly doing a GROUP BY or if there is any other way I can get the desired result I would be very greatful.

推荐答案

我知道我在这里回答自己的问题，但是...

I know I am answering my own question here but...

这最终使其正常工作(感谢: http://www.getrouty.com/mysql-insert-select-on-duplicate-update-using-aggregate-functions/)

This eventually got it working (thanks to: http://www.getrouty.com/mysql-insert-select-on-duplicate-update-using-aggregate-functions/)

INSERT INTO SomeTable(field1, field2) 
SELECT 'blah' AS field1, 
    MAX(AnotherTable.number) AS field2
FROM AnotherTable
ON DUPLICATE KEY 
UPDATE field2 = values(field2)

这篇关于您可以在ON DUPLICATE KEY中使用汇总值吗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！