How can I reset an MySQL AutoIncrement using a MAX value from another table?

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?

Contents hide

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

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;

Method 2

Following the MySQL documentation, this worked for me in MySQL 5.7:

SET @m = (SELECT MAX(id) + 1 FROM ABC);
SET @s = CONCAT('ALTER TABLE XYZ AUTO_INCREMENT=', @m);
PREPARE stmt1 FROM @s;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;

Method 3

Whoever is having a problem with PREPARE stmt FROM ‘ALTER TABLE XYZ AUTO_INCREMENT = ?’ can use:

CREATE PROCEDURE reset_xyz_autoincrement
BEGIN
      SELECT @max := MAX(ID)+ 1 FROM ABC;
      set @alter_statement = concat('ALTER TABLE temp_job_version AUTO_INCREMENT = ', @max);
      PREPARE stmt FROM @alter_statement;
      EXECUTE stmt;
      DEALLOCATE PREPARE stmt;
END

Method 4

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;

Method 5

Reset Auto Increment IDs.

Reset Auto Increment IDs

Update all auto increment columns in a database to the smallest possible value based on current values in the databases. We needed to do this after cleaning out a database.

Use a prepared statement within a stored procedure:

drop PROCEDURE if exists reset_autoincrement;
DELIMITER //
CREATE PROCEDURE reset_autoincrement (IN schemaName varchar(255))
 BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE o_name VARCHAR(255);
    DECLARE o_table VARCHAR(255);
    DECLARE cur1 CURSOR FOR SELECT COLUMN_NAME, TABLE_NAME FROM information_schema.`COLUMNS` WHERE extra LIKE '%auto_increment%' and table_schema=schemaName;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
    OPEN cur1;
    read_loop: LOOP
     FETCH cur1 INTO o_name, o_table;

     IF done THEN
       LEAVE read_loop;
     END IF;

  set @qry1 = concat('SELECT MAX(`',o_name,'`) + 1 as autoincrement FROM `',o_table,'` INTO @ai');
  PREPARE stmt1 FROM @qry1;
  EXECUTE stmt1;

  IF @ai IS NOT NULL THEN
      SELECT  o_name, o_table;
   select @qry1;
   select @ai;
   set @qry2 = concat('ALTER TABLE `',o_table,'` AUTO_INCREMENT = ', @ai);
   select @qry2;
   PREPARE stmt2 FROM @qry2;
   EXECUTE stmt2;
  END IF;

    END LOOP;

    CLOSE cur1;
 END //
DELIMITER ;


call reset_autoincrement('my_schema_name');

Method 6

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.

Method 7

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);

Method 8

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

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes

Article Rating