Can you use aggregate values within ON DUPLICATE KEY

Question

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.

Answer 1

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)

Answer 2

Please have a try if this works:

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

Not sure if 2 times the subquery is needed. I usually use this to circumvent MySQLs limitation to not be able to update the table with values I read from the same table. Not sure if this works here, too.