MYSQL & innoDB alter dynamically AUTO_INCREMENT of a table -

MYSQL & innoDB alter dynamically AUTO_INCREMENT of a table -

i have problem, illustration in scheme have next table:

create table `sales` ( `id` int(11) not null auto_increment, `amount` float not null, primary key (`id`) ) engine=innodb; -- more complex table

with content:

+-----+-------+ | id | amount| +-----+-------+ |2023 | 100 | |2024 | 223 | |2025 | 203 | |... | |2505 | 324 | +-----+-------+

i don't know current id(there sales every day). i'm trying normalize table.

update sales set id=id - 2022;

result:

+-----+-------+ | id | amount| +-----+-------+ | 1 | 100 | | 2 | 223 | | 3 | 203 | |... | | 482 | 324 | +-----+-------+ the problem

my problem trying alter auto_increment, f.e.:

alter table sales auto_increment = 483;

its right don't know current id :(, seek next query:

alter table sales auto_increment = (select max(id) sales );

this causes me error(#1064). reading documentation tells me:

in mysql, cannot modify table , select same table in subquery.

http://dev.mysql.com/doc/refman/5.7/en/subqueries.html

i seek whit variables:

set @new_index = (select max(id) sales ); alter table sales auto_increment = @new_index;

but, causes error :(.

alter table must have literal values in time statement parsed (i.e. @ prepare time).

you can't set variables or parameters statement @ parse time, can set variables statement before parse time. , means using dynamic sql:

set @new_index = (select max(id) sales ); set @sql = concat('alter table sales auto_increment = ', @new_index); prepare st @sql; execute st;

mysql innodb auto-increment mysql-5.7