Insert multiple rows from a select and put the generated IDs into other table

Question

Given MySQL tables something like this¹:

alpha (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, beta_id INT NULL
, type_info INT NOT NULL
, CONSTRAINT fk_alpha_beta FOREIGN KEY (beta_id) REFERENCES beta (id)
) ENGINE=InnoDB;

beta (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
, type_info INT NOT NULL
) ENGINE=InnoDB;

My alpha table contains many rows, and I want to create corresponding rows in table beta, and store the ids in the alpha.beta_id column.

I can create the rows in beta in one shot like this:

INSERT INTO beta (type_info)
SELECT type_info FROM alpha
WHERE beta_id IS NULL;

How can I do this in such a way that I can put the generated beta ids into the beta_id columns of the corresponding rows in alpha?

The rows in beta do not have unique characteristics that would allow me to identify them retrospectively.

If it were only one row I was creating, I could use something like:

UPDATE alpha
SET beta_id=LAST_INSERT_ID()
WHERE beta_id IS NULL;

But that does not seem to be generalisable to multiple rows.

This question is vaguely similar, but it is using INSERT ... VALUES and isn't applicable to a use case with INSERT ... SELECT.

This question has similar requirements, and I know I can use ROW_COUNT to see how many rows were inserted, but that doesn't give me a correspondence between the rows I added and the rows in alpha that they were created for.

I'm left with writing a non-SQL script that will pull all the information out, create the beta rows one at a time, and then update rows in alpha individually. But I would rather do this in-SQL, and it seems like a fairly obvious use case.

---

^{1. Obviously our actual schema is somewhat more complicated, but I have tried to boil it down to the information necessary to demonstrate the question.}

Answer 1

If there is a one-to-one mapping, you can join back using the type column:

update alpha a join
       beta b 
       on a.type_info = b.type_info
    set a.beta_id = b.id;

EDIT:

MySQL doesn't have great facilities for this. But you can add an alpha_id (perhaps temporarily) and do:

alter table beta add column alpha_id int;

INSERT INTO beta (alpha_id, type_info)
    SELECT a.id, a.type_info
    FROM alpha a
    WHERE beta_id IS NULL;

update alpha a join
       beta b 
       on a.id = b.alpha_id
    set a.beta_id = b.id;

You can remove the column after the update, if you want.

Answer 2

You can assume that a multi-row INSERT generates consecutive auto-increment id's, unless innodb_auto_inc_lock_mode=2. See https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html

LAST_INSERT_ID() will tell you the first id generated by the insert, and the others will each be 1 greater, as many as the number of rows inserted.

Then you can UPDATE alpha to set these, but be sure to use ORDER BY to specify both the order of copying and the order of UPDATE so they remain the same.

INSERT INTO beta (type_info)
SELECT type_info FROM alpha
WHERE beta_id IS NULL
ORDER BY id;

SET @id = LAST_INSERT_ID()-1;

UPDATE alpha
SET beta_id=(@id := @id + 1)
WHERE beta_id IS NULL
ORDER BY id;