5

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.

rioubenson
  • 401
  • 1
  • 4
  • 16

2 Answers2

8

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)
Nae
  • 14,209
  • 7
  • 52
  • 79
rioubenson
  • 401
  • 1
  • 4
  • 16
  • 1
    Ah, the values thing, of course. Forgot about that. +1 from me :) – fancyPants Jul 24 '13 at 15:26
  • 3
    Note that it's the column name of the table you're inserting things into that you should pass into the values() function, not the name of the aggregate field. @user1491016 It might be worth renaming your MAX() field to max_field2 to make that clearer. Either way you sent me in the right direction, thanks for the solution :) – adavea Feb 20 '14 at 18:27
  • How does this work in MySQL 8.xx? I get the warning 1287 "'VALUES function' is deprecated and will be removed in a future release. Please use an alias (INSERT INTO ... VALUES (...) AS alias) and replace VALUES(col) in the ON DUPLICATE KEY UPDATE clause with alias.col instead". But in this kind of statement we have a subquery... – rantanplan Jan 19 '21 at 14:03
1

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.

fancyPants
  • 50,732
  • 33
  • 89
  • 96
  • Sadly, that doesn't work either. It doesnt recognise AnotherTable.field2. I did try aliasing the select, but MySql had a fit with that as well. – rioubenson Jul 24 '13 at 15:13
  • Edited my answer, please have a try again. – fancyPants Jul 24 '13 at 15:17
  • Thanks fancyPants. After trawling google I came accross this: http://www.getrouty.com/mysql-insert-select-on-duplicate-update-using-aggregate-functions/ and it seems to work if I add values. I'll add the SQL below. – rioubenson Jul 24 '13 at 15:22