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?
asked Mar 9, 2010 at 16:37
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;
answered Mar 9, 2010 at 19:13
OMG PoniesOMG Ponies
318k78 gold badges511 silver badges494 bronze badges
7
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;
answered Jan 4, 2017 at 14:57
SaeidSaeid
2,5945 gold badges19 silver badges20 bronze badges
1
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
answered Jul 12, 2013 at 14:24
AnuragAnurag
98010 silver badges30 bronze badges
2
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;answered Jun 3, 2010 at 13:47
user357516user357516
911 silver badge1 bronze badge
0
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');
answered Feb 19, 2015 at 20:45
ArtistanArtistan
1,92221 silver badges33 bronze badges
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 9, 2010 at 16:52
Andy ShellamAndy Shellam
15.2k1 gold badge25 silver badges41 bronze badges
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 9, 2010 at 17:30
ThinkCodeThinkCode
7,56919 gold badges71 silver badges91 bronze badges
1
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.sqlanswered Mar 9, 2010 at 17:36
Ike WalkerIke Walker
62.8k14 gold badges106 silver badges108 bronze badges
0