Search code examples
mysqlsqldatabaseauto-increment

Set AutoIncrement value with Subquery in MySQL

I am trying to provide the value for auto increment value through the subquery in mysql

I tried with this command

alter table xxx auto_increment = (select max(id) from xxx) ;

But am getting the syntax error as

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(select max(id) from xxx)' at line 1

Please anyone help me on this issue....Thanks in advance

Solution

  • You need to use dynamic sql for this:

    select concat("alter table foo auto_increment=",max(id)) from foo into @maxid;
prepare setautoinc from @maxid;
execute setautoinc;
deallocate prepare setautoinc;

    fiddle