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

Question

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 1

Personally I'd probably use either a shell script or a little C#/C++ application or PHP/Ruby/Perl script to do this in 2 queries:

• Grab the value you want SELECT MAX(ID) FROM ABC;
• Alter the table using the value ALTER TABLE XYZ AUTO_INCREMENT = <insert value retrieved from first query here>

Obviously being careful that the new auto increment won't cause any key clashes with existing data in the XYZ table.

Answer 2

Ok guys. I have come up with a not so intuitive solution. The best part is that it works!

SELECT @max := max(ID) from ABC;       
ALTER TABLE XYZ AUTO_INCREMENT = 1;
ALTER TABLE XYZ ADD column ID INTEGER primary key auto_increment;
UPDATE XYZ SET ContactID = (ContactID + @max);

Answer 3

If you really want to do this in MySQL alone, you can just dump the dynamically built alter command to a file on disk and then execute it.

Like so:

select concat('ALTER TABLE XYZ AUTO_INCREMENT = ',max(ID)+1,';') as alter_stmt
into outfile '/tmp/alter_xyz_auto_increment.sql'
from ABC;

\. /tmp/alter_xyz_auto_increment.sql

Answer 4

Use a Prepared Statement within a Stored Procedure:

--DROP PROCEDURE IF EXISTS ...
CREATE PROCEDURE reset_xyz_autoincrement
BEGIN

  SELECT @max := MAX(ID)+ 1 FROM ABC; 

  PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT = ?'
  EXECUTE stmt USING @max 

  DEALLOCATE PREPARE stmt;

END $$

Answer 5

http://stackoverflow.com/questions/2361982/create-table-new-table-name-like-old-table-name-with-old-table-names-auto-increm/2362599#2362599

Answer 6

I'm creating an automated database transformation script for a new version of my application.

In one table, I needed to change the primary auto-increment field to a different field. Since this page came up first many times while I googled a solution for it, here's a solution that eventually worked for me:

-- Build a new ID field from entry_id, make it primary and fix the auto_increment for it:
ALTER TABLE  `entries` ADD  `id` INT UNSIGNED NOT NULL FIRST;
UPDATE entries SET id = entry_id;
ALTER TABLE  `entries` ADD PRIMARY KEY (  `id` );

-- ...the tricky part of it:
select @ai := (select max(entry_id)+1 from entries);
set @qry = concat('alter table entries auto_increment=',@ai);
prepare stmt from @qry; execute stmt;

-- ...And now it's possible to switch on the auto_increment:
ALTER TABLE  `entries` CHANGE  `id`  `id` INT( 10 ) UNSIGNED NOT NULL AUTO_INCREMENT;