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

Question

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?

score 40 · Accepted Answer · edited Aug 05 '21 at 20:01

40

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;

edited Aug 05 '21 at 20:01

Peter Mortensen

30,738
21
105
131

answered Mar 09 '10 at 19:13

OMG Ponies

325,700
82
523
502

Sounds like a pretty solid answer for my question! Thank you! – ThinkCode Mar 09 '10 at 21:28
14

`PREPARE stmt FROM 'ALTER TABLE XYZ AUTO_INCREMENT = ?'` generate error for me :( – bor Aug 02 '12 at 08:56
1

The error is just `You have an error in your SQL syntax ... near '?' at line 1`. You can see it in [this fiddle](http://rextester.com/PPLB35064). It seems that the `?` is not accepted in the PREPARE statement with `AUTO_INCREMENT`. The [Anurag](http://stackoverflow.com/a/17617206/1677209) answer has a workaround for it. – T30 Nov 16 '16 at 10:52
2

@T30 I got the same error with '?', I have posted a workaround for this [here](http://stackoverflow.com/a/41466825/2822296) – Saeid Jan 04 '17 at 15:08
In the answer, the same table ABC is used for both getting MAX(ID) and in the prepared statement to alter table. This causes effectively no change. @ThinkCode wanted to set auto_increment of another table. – Chetan Khilosiya Aug 29 '17 at 06:22
I use MySQL 8 and received the same error as @T30. Using Saeed's answer it worked. – Gabe Gates Oct 15 '19 at 14:39
@ChetanKhilosiya You are not correct. Deleting from the table will cause gaps in IDs and if you delete from the end of the table MAX(ID) can be much lower than the AUTO_INCREMENT – Izzy Aug 05 '21 at 11:52

score 33 · Answer 2 · edited Aug 05 '21 at 19:48

33

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;

edited Aug 05 '21 at 19:48

Peter Mortensen

30,738
21
105
131

answered Jan 04 '17 at 14:57

Saeid

2,704
5
20
20

3

Might need `SELECT COALESCE(MAX(id), 0) + 1 FROM ABC` if ABC is an empty table. – John Vinopal Jun 14 '19 at 00:41

score 11 · Answer 3 · edited Aug 05 '21 at 19:55

11

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

edited Aug 05 '21 at 19:55

Peter Mortensen

30,738
21
105
131

answered Jul 12 '13 at 14:24

Anurag

1,013
11
30

I had high hopes for this one, but I get this error: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN SELECT @max := MAX(id)+ 1 FROM test' at line 2 – kloddant Dec 13 '16 at 15:44
It is missing brackets on the first line. Make it "CREATE PROCEDURE reset_xyz_autoincrement ()" – Captain Payalytic Mar 14 '18 at 13:59

score 9 · Answer 4 · answered Jun 03 '10 at 13:47

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;

score 6 · Answer 5 · edited Aug 05 '21 at 19:53

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

score 2 · Answer 6 · answered Mar 09 '10 at 16:52

2

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.

answered Mar 09 '10 at 16:52

Andy Shellam

15,403
1
27
41

1

I thought it can easily be done by using only MySQL queries. Looks like I am out of luck. Any other solution using only MySQL - using variables in MySQL? I want to execute a bunch of queries in one .sql file rather than shell scripts and Perl/Python scripts. Thanks for the answer. – ThinkCode Mar 09 '10 at 16:59

score 1 · Answer 7 · answered Mar 09 '10 at 17:30

1

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

answered Mar 09 '10 at 17:30

ThinkCode

7,841
21
73
92

1

Where does ContactID comes from? – T30 Nov 11 '16 at 10:50

score 1 · Answer 8 · answered Mar 09 '10 at 17:36

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

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

8 Answers8

Linked