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
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.
Yes, it is possible to use UPDATE query with LIMIT in MySQL.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With