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

sql mysql auto-increment reset
ThinkCode picture ThinkCode · Mar 9, 2010 · Viewed 42.8k times · Source

I know this won't work, 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. Thank you!

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?

Answer

OMG Ponies picture OMG Ponies · Mar 9, 2010

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;

Related questions

How to reset AUTO_INCREMENT in MySQL?

How can I reset the AUTO_INCREMENT of a field? I want it to start counting from 1 again.

Read More
Add Auto-Increment ID to existing table?

I have a pre-existing table, containing 'fname', 'lname', 'email', 'password' and 'ip'. But now I want an auto-increment column. However, when I enter: ALTER TABLE users ADD id int NOT NULL AUTO_INCREMENT I get the following: #1075 - Incorrect table …

Read More
Auto Increment after delete in MySQL

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state …

Read More