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

CodePudding user response：

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

CodePudding user response：

You cannot set AUTO_INCREMENT by this way - new AUTO_INCREMENT value is literal and cannot be an expression.

To achieve your goal it is enough to set AUTO_INCREMENT to 1. For this statement the provided value is always checked, and if some rows exists and it must be above provided value then it is adjusted to MAX(AI_column) 1 automatically.

DEMO:

CREATE TABLE test ( id INT AUTO_INCREMENT PRIMARY KEY );
INSERT INTO test VALUES (DEFAULT), (DEFAULT);
SELECT * FROM test;      -- rows id=1,2 inserted
SHOW CREATE TABLE test;  -- AUTO_INCREMENT = 3 

ALTER TABLE test AUTO_INCREMENT = 10;           -- set value above current
SHOW CREATE TABLE test;  -- AUTO_INCREMENT = 10 -- specified value applied

ALTER TABLE test AUTO_INCREMENT = 1;           -- set value below current
SHOW CREATE TABLE test;  -- AUTO_INCREMENT = 3 -- value adjusted to MAX(id) 1

fiddle