How to set AUTO_INCREMENT from another table

Question

How can I set the AUTO_INCREMENT on CREATE TABLE or ALTER TABLE from another table?

I found this question, but not solved my issue: How to Reset an MySQL AutoIncrement using a MAX value from another table?

I also tried this:

CREATE TABLE IF NOT EXISTS `table_name` (
  `id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `columnOne` tinyint(1) NOT NULL,
  `columnTwo` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=(SELECT `AUTO_INCREMENT` FROM  `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'database_name' AND `TABLE_NAME` = 'another_table_name');

this:

ALTER TABLE `table_name` AUTO_INCREMENT=(SELECT `AUTO_INCREMENT` FROM  `INFORMATION_SCHEMA`.`TABLES` WHERE `TABLE_SCHEMA` = 'database_name' AND `TABLE_NAME` = 'another_table_name');

this:

CREATE TABLE IF NOT EXISTS `table_name` (
  `id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `columnOne` tinyint(1) NOT NULL,
  `columnTwo` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=(SELECT (MAX(`id`)+1) FROM `another_table_name`);

and this:

ALTER TABLE `table_name` AUTO_INCREMENT=(SELECT (MAX(`id`)+1) FROM `another_table_name`);

Why didn't the accepted answer to the linked question solve your issue? — Álvaro González, Sep 13 '15 at 15:30
possible duplicate of [How to Reset an MySQL AutoIncrement using a MAX value from another table?](http://stackoverflow.com/questions/2410689/how-to-reset-an-mysql-autoincrement-using-a-max-value-from-another-table) — Álvaro González, Sep 14 '15 at 06:15

asd-tm · Accepted Answer · 2015-09-13T15:20:06.770

5

This code will create procedure for you:

CREATE PROCEDURE `tbl_wth_ai`(IN `ai_to_start` INT)
BEGIN

SET @s=CONCAT('CREATE TABLE IF NOT EXISTS `table_name` (
  `id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `columnOne` tinyint(1) NOT NULL,
  `columnTwo` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT = ', `ai_to_start`);

  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END;

Then you may call CALL tbl_wth_ai(2); passing the parameter inside the brackets.

For example:

CALL tbl_wth_ai((SELECT id FROM `ttest` WHERE c1='b'));

edited Sep 13 '15 at 15:20

answered Sep 13 '15 at 15:13

asd-tm

3,381
2
24
41

Thanks, but I get this error message: `ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4` – Evren Sep 13 '15 at 15:53
@Codename - I guess you aren't changing the deliminter. Is this the reason why you didn't consider the answers you've linked? – Álvaro González Sep 13 '15 at 16:17
@ÁlvaroG.Vicario: Which deliminter you mean, `'...'`? I also tried it with `"..."` – Evren Sep 13 '15 at 17:02
@Codename It seems you are calling both queries (procedure creation and its calling) in a single query. Read [this answer at stackowerflow](http://stackoverflow.com/a/10259528/5043424). In simple. you may start your code with `DELIMITER $$` then separate the two queries by `$$` and set `DELIMITER ;` at the end of the whole double query statement. The code given in my answer does not contain any syntax errors. It works fine and was copied and pasted from my MySQL IDE after being successfully run. – asd-tm Sep 13 '15 at 18:29
@asd-tm: Thank you very much! – Evren Sep 13 '15 at 19:58

score 1 · Answer 2 · answered Nov 14 '17 at 12:26

1

SELECT `AUTO_INCREMENT` INTO @AutoInc
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'my_db' AND TABLE_NAME = 'old_table';

SET @s:=CONCAT('ALTER TABLE `my_db`.`new_table` AUTO_INCREMENT=', @AutoInc);
PREPARE stmt FROM @s;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

answered Nov 14 '17 at 12:26

serghei

3,069
2
30
48

Thank you for this code snippet, which might provide some limited, immediate help. A proper explanation [would greatly improve](//meta.stackexchange.com/q/114762) its long-term value by showing *why* this is a good solution to the problem, and would make it more useful to future readers with other, similar questions. Please [edit] your answer to add some explanation, including the assumptions you've made. – Toby Speight Nov 14 '17 at 13:07

score 0 · Answer 3 · answered Sep 13 '15 at 20:01

This works fine:

DELIMITER $$
CREATE PROCEDURE `tbl_wth_ai`(IN `ai_to_start` INT)
BEGIN

SET @s=CONCAT('CREATE TABLE IF NOT EXISTS `table_name` (
  `id` mediumint(6) unsigned NOT NULL AUTO_INCREMENT,
  `columnOne` tinyint(1) NOT NULL,
  `columnTwo` int(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT = ', `ai_to_start`);

  PREPARE stmt FROM @s;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;
END $$
DELIMITER ;

CALL tbl_wth_ai((SELECT MAX(`id`)+1 FROM `another_table_name`));
DROP PROCEDURE IF EXISTS tbl_wth_ai;

How to set AUTO_INCREMENT from another table

3 Answers3

Linked