Reset auto increment column value in script mysql

2
votes

I have two mysql tables, one needs to start its auto-increment column id with the last value of the last inserted row in the other table (plus 1).

According to mysql manual you can restart the value of an auto increment column like this:

mysql> ALTER TABLE tbl AUTO_INCREMENT = 100;

However, this is not possible:

mysql> ALTER TABLE tb2 AUTO_INCREMENT = (SELECT MAX(id) FROM tbl1);

I need to perform something like this because I'm filling the tables using a script. Is there another way to achieve it?

3
mysqlauto-increment
The need to manipulate the auto_increment index is usually the symptom of a design flaw. Why do the ids of the tables need to correlate in this way? - Pekka
Don't think that SELECT statements work in this situation. You can always query it from tbl1 first. By default the auto increment is set to the latest entry in that table though. So be careful that you don't overwrite values in tbl2 with those tbl1 — they might already exist in tbl2. If you need them to be the same, don't use auto increment altogether and just update the id in tbl2 with the one in tbl1. - Alec
Both tables are related to a third table that will generate the ids of their rows in the future. As I have to migrate data using a script is easier for me to fill one table, then the second one, then fill the generator table and then create the foreign keys. - Lucia

3 Answers

2
votes

I think you are trying to use the auto-increment column for something it is not well suited to. If you care about the exact values that get inserted then it shouldn't be auto-increment.

0
votes

IT IS USING THIS SCRIPT 

ALTER TABLE `people` AUTO_INCREMENT =1

where people is my table, or you can do this through GUI (operation tab of phpmyadmin with current table selected)

0
votes

What about create a dummy record and increment?

-- insert dummy forcing id
INSERT INTO 'tb2' (ID, NAME, ...)
VALUES (SELECT MAX(id) FROM tb1, "dummy", ...);

-- automagically increment to last id + 1
ALTER TABLE `tb2' AUTO_INCREMENT = 1; -- only myISAM

-- delete dummy
DELETE FROM 'tb2' WHERE NAME="dummy";