change auto_increment within same table using subquery mysql

Question

I am using mysql. I have a database table with auto_increment counter set. Now because of a requirement I need to leave starting 100 ids free and move all existing records starting from 101, so current id 1 will go to 101 and id 2 will become 102 and so on.

I am able to move records to 101 but the problem is that how to change auto_increment counter to max(id)+1.

Main constraint here with me is that I need to do it in single sql statement. I can not save the value using @counter and use it later.

I tried using below query

ALTER TABLE role AUTO_INCREMENT = (SELECT rd.counter FROM (SELECT (MAX(id) + 1) AS counter FROM role r) rd);

But it is not working.

Answer 1

The parser does not support a subquery in the place you are trying to use it.

Here's the excerpt from the MySQL source, from sql/sql_yacc.yy:

create_table_option:
    . . .
    | AUTO_INC opt_equal ulonglong_num

What you should read there is that the AUTO_INCREMENT table option accepts only a single literal number, not an expression or a subquery or a variable or anything else. So you simply can't set the AUTO_INCREMENT in the same statement in which you do SELECT MAX(id)+1.

But you don't have to.

MySQL will never allocate an auto-increment id less than the largest value currently in the table. So if you have a table with id value 102, the next value allocated will be at least 103.

You can even try to set AUTO_INCREMENT=50 explicitly, but that will be increased automatically to MAX(id)+1.