Update autoincrement field to highest value + 1

Question

How can I update an existing row's id (autoincrement) field to give it the value that would normally be automatically assigned to a new insert (i.e., highest id + 1)?

http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html states:

"...You can also explicitly assign 0 to the column to generate sequence numbers."

But this doesn't appear to be true. I've tried assigning a value of 0 and it does just that.. asigns a value of 0.

I've considered deleting and re-inserting the row but this seems messy.

Also tried:

UPDATE example_table
SET id = (SELECT id+1 FROM example_table ORDER BY id DESC LIMIT 1)
WHERE id = 1000;

Which returns the error: You can't specify target table 'example_table' for update in FROM clause

and:

UPDATE example_table SET id = max(id)+1 WHERE id = 1000;

Which returns the error: Invalid use of group function

This seems like it should be easy... what am I missing?

Answer 1

MySQL doesn't allow using table being updated in a from clause. you can have it as sub query

UPDATE example_table
     SET id= (SELECT maxId
              FROM (SELECT MAX(id) + 1 AS maxId
                    FROM example_table
                   ) t
              )
    WHERE id = 1000;