mysql query to update field to max(field) + 1

Question

What I want to do is:

UPDATE table SET field = MAX(field) + 1 WHERE id IN (1, 3, 5, 6, 8);

The semantics of this statement, in my mind, would be first the database would go off and determine for me what the largest value of field is in all of table. It would then add 1 to that value, and assign the resulting value to the field column of the rows with id 1, 3, 5, 6, and 8. Seems simple enough...

When I try to run that query though, MySQL chokes on it and says:

ERROR 1111 (HY000): Invalid use of group function

What's the secret sauce you have to use to get the outcome I desire?

Regards, Vic

Answer 1

I don't have a mysql database to test this on, but I would try using a subquery.

update table set field = (select max(field) + 1 from table) where id in (1, 3, 5, 6, 8);

Answer 2

In order to get around the mysql-error-1093, use a subquery/derived table/inline view:

UPDATE table
      SET field = (SELECT x.max_field
                          FROM (SELECT MAX(t.field) + 1 AS max_field
                                        FROM TABLE t
                                       WHERE t.id IN (1,3,5,6,8) x)

Answer 3

Try

UPDATE TABLE set field = ((SELECT selected_value FROM (SELECT MAX(field) AS selected_value FROM table) AS sub_selected_value) + 1) WHERE id in (1,3,5,6,8)

Blatantly ripped off from Here