Can you use aggregate values within ON DUPLICATE KEY
mysql, sql
Solution
I know I am answering my own question here but...
This eventually got it working (thanks to: a broken link)
INSERT INTO SomeTable(field1, field2)
SELECT 'blah' AS field1,
MAX(AnotherTable.number) AS field2
FROM AnotherTable
ON DUPLICATE KEY
UPDATE field2 = values(field2)
Problem
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) ``` I get Error Code: 1111 Invalid use of group function. Reading through the MySql documentation: http://dev.mysql.com/doc/refman/5.1/en/insert-select.html the lines of interest are: "In the values part of ON DUPLICATE KEY UPDATE, you can refer to columns in other tables, as long as you do not use GROUP BY in the SELECT part. One side effect is that you must qualify nonunique column names in the values part. " 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. 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.