Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

like image 671
vicatcu Avatar asked Jul 26 '10 03:07

vicatcu


People also ask

How do you UPDATE a field from another field in SQL?

UPDATE table SET col = new_value WHERE col = old_value AND other_col = some_other_value; UPDATE table SET col = new_value WHERE col = old_value OR other_col = some_other_value; As you can see, you can expand the WHERE clause as much as you'd like in order to filter down the rows for updating to what you need.

Can we use limit in UPDATE query MySQL?

Yes, it is possible to use UPDATE query with LIMIT in MySQL.


1 Answers

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)
like image 185
GWW Avatar answered Sep 17 '22 18:09

GWW