How to Reset MySQL AutoIncrement using a MAX value from another table?


You can use prepare statement to Reset MySQL AutoIncrement using a MAX value from another table.

The following is the syntax −

set @anyVariableName1=(select MAX(yourColumnName) from yourTableName1);
SET @anyVariableName2 = CONCAT('ALTER TABLE yourTableName2
AUTO_INCREMENT=', @anyVariableName1);
PREPARE yourStatementName FROM @anyVariableName2;
execute yourStatementName;

The above syntax will reset MySQL auto_increment using a maximum value from another table. To understand the above syntax, let us create two tables. The first table will contain the records and the second table will use the maximum value from the first table and use for an auto_increment property.

The query to create a table is as follows −

mysql> create table FirstTableMaxValue
   -> (
   -> MaxNumber int
   -> );
Query OK, 0 rows affected (0.64 sec)

Insert records from the table using insert command. The query is as follows −

mysql> insert into FirstTableMaxValue values(100);
Query OK, 1 row affected (0.15 sec)

mysql> insert into FirstTableMaxValue values(1000);
Query OK, 1 row affected (0.19 sec)

mysql> insert into FirstTableMaxValue values(2000);
Query OK, 1 row affected (0.12 sec)

mysql> insert into FirstTableMaxValue values(90);
Query OK, 1 row affected (0.15 sec)

mysql> insert into FirstTableMaxValue values(2500);
Query OK, 1 row affected (0.17 sec)

mysql> insert into FirstTableMaxValue values(2300);
Query OK, 1 row affected (0.12 sec)

Display all records from the table using select statement. 

The query is as follows −

mysql> select *from FirstTableMaxValue;

Output

+-----------+
| MaxNumber |
+-----------+
|       100 |
|      1000 |
|      2000 |
|        90 |
|      2500 |
|      2300 |
+-----------+
6 rows in set (0.05 sec)

Now you can create a second table. The query to create a second table is as follows −

mysql> create table AutoIncrementWithMaxValueFromTable
   -> (
   -> ProductId int not null auto_increment,
   -> Primary key(ProductId)
   -> );
Query OK, 0 rows affected (1.01 sec)

Here I am going to include a statement that will get the max value from the first table and set the max value to an auto_increment property for second table. The query is as follows −

mysql> set @v=(select MAX(MaxNumber) from FirstTableMaxValue);
Query OK, 0 rows affected (0.00 sec)

mysql> SET @Value2 = CONCAT('ALTER TABLE AutoIncrementWithMaxValueFromTable
AUTO_INCREMENT=', @v);
Query OK, 0 rows affected (0.00 sec)

mysql> PREPARE myStatement FROM @value2;
Query OK, 0 rows affected (0.29 sec)
Statement prepared

mysql> execute myStatement;
Query OK, 0 rows affected (0.38 sec)
Records: 0 Duplicates: 0 Warnings: 0

Now we have added max value which is 2500 from the first table to the second table. Now you can insert record in the table which starts from 2500, 2501 and so on.

The query to insert records in the second table is as follows −

mysql> insert into AutoIncrementWithMaxValueFromTable values();
Query OK, 1 row affected (0.24 sec)

mysql> insert into AutoIncrementWithMaxValueFromTable values();
Query OK, 1 row affected (0.10 sec)

Check all records from the table using select command. The query is as follows −

mysql> select *from AutoIncrementWithMaxValueFromTable;

Output

+-----------+
| ProductId |
+-----------+
|      2500 |
|      2501 |
+-----------+
2 rows in set (0.00 sec)

Updated on: 30-Jul-2019

868 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements