auto increment - change auto_increment within same table using subquery mysql -

i using mysql. have database table auto_increment counter set. because of requirement need leave starting 100 ids free , move existing records starting 101, current id 1 go 101 , id 2 become 102 , on.

i able move records 101 problem how change auto_increment counter max(id)+1.

main constraint here me need in single sql statement. can not save value using @counter , use later.

i tried using below query

alter table role auto_increment = (select rd.counter (select (max(id) + 1) counter role r) rd); 

but not working.

the parser not support subquery in place trying use it.

here's excerpt mysql source, sql/sql_yacc.yy:

create_table_option:     . . .     | auto_inc opt_equal ulonglong_num 

what should read there auto_increment table option accepts single literal number, not expression or subquery or variable or else. can't set auto_increment in same statement in select max(id)+1.

but don't have to.

mysql never allocate auto-increment id less than largest value in table. if have table id value 102, next value allocated at least 103.

you can try set auto_increment=50 explicitly, increased automatically max(id)+1.