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.