问题描述
我正在尝试编写类似于以下内容的查询:
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: 1111Invalid 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中使用汇总值吗的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!