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

score 30 · Accepted Answer · edited Aug 19 '19 at 13:30

30

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)

edited Aug 19 '19 at 13:30

Agnel Vishal

564
6
13

answered Jul 26 '10 at 03:41

GWW

43,129
11
115
108

1

You did beat me, but you are missing a closing bracket :P – GWW Jul 26 '10 at 04:58

score 9 · Answer 2 · edited Oct 29 '16 at 23:22

9

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)

edited Oct 29 '16 at 23:22

Nisse Engström

4,738
23
27
42

answered Jul 26 '10 at 03:40

OMG Ponies

325,700
82
523
502

You're missing a bracket after `t.id IN (1,3,5,6,8) **)** x)` – Ultrazz008 Oct 29 '16 at 20:05

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

2 Answers2

Linked

Related