Question 112996 in Mysql Question posted in
The official MySQL documentation can be found here.

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

Tags:

2

Answers


  1. 0

    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
    id
    1
    2
    Table Create Table
    test CREATE TABLE `test` (
      `id` int NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci    		 
    ALTER TABLE test AUTO_INCREMENT = 10;           -- set value above current
SHOW CREATE TABLE test;  -- AUTO_INCREMENT = 10 -- specified value applied
    Table Create Table
    test CREATE TABLE `test` (
      `id` int NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci    		 
    ALTER TABLE test AUTO_INCREMENT = 1;           -- set value below current
SHOW CREATE TABLE test;  -- AUTO_INCREMENT = 3 -- value adjusted to MAX(id)+1
    Table Create Table
    test CREATE TABLE `test` (
      `id` int NOT NULL AUTO_INCREMENT,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

    fiddle

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search