0

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

RiggsFolly
  • 93,638
  • 21
  • 103
  • 149
Kalaiarasu M
  • 31
  • 3

2 Answers2

1

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

Akina
  • 39,301
  • 5
  • 14
  • 25
1

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

ysth
  • 96,171
  • 6
  • 121
  • 214
  • @Akina only if the value you are setting exists. Just `alter table foo auto_increment = 123` does the same – ysth Oct 26 '22 at 17:39
  • As I have said in my answer - if provided or above value exists. From the other side - you select this value, so it cannot not exist :) – Akina Oct 26 '22 at 17:44