Menu
I know this won't work. I tried it in various forms and failed all times. What is the simplest way to achieve the following result?
ALTER TABLE XYZ AUTO_INCREMENT = (select max(ID) from ABC); This is great for automation projects.
SELECT @max := (max(ID)+1) from ABC; -> This works! select ID from ABC where ID = (@max-1); -> This works! ALTER TABLE XYZ AUTO_INCREMENT = (@max+1); -> This fails :( Why? 
314
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
The TRUNCATE TABLE statement removes all the data from a table and resets the auto-increment value to zero.
What will happen if the MySQL AUTO_INCREMENT column reaches the upper limit of the data type? When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails.
Using the ALTER TABLE Statement. The ALTER TABLE statement is used to change the name of a table or any table field. It is also used to add, delete, or reset an existing column in a table. MySQL also allows this statement to reset the auto-increment column value whenever we want.
Use a prepared statement:
SELECT @max := MAX(ID)+ 1 FROM ABC; PREPARE stmt FROM 'ALTER TABLE ABC AUTO_INCREMENT = ?'; EXECUTE stmt USING @max; DEALLOCATE PREPARE stmt; If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
