vicatcu vicatcu - 1 month ago 6
MySQL Question

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

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

GWW GWW
Answer

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

Comments