Vittorio Romeo Vittorio Romeo - 14 days ago 5
MySQL Question

"Insert...select" transactions with multiple values and normalized database

This question properly explains how to simultaneously insert data in a database for "hierarchical" structures (is-a relationship):


No, you can't insert into multiple tables in one MySQL command. You
can however use transactions.

BEGIN;
INSERT INTO base_class (data0, data1) VALUES('0', '1');
INSERT INTO derived_class (base_id, data2, data3) VALUES(LAST_INSERT_ID(), '2', '3');
COMMIT;



The solution works well when inserting things one at a time.

I'm, however, in a situation where I need to insert multiple values at the same time using
INSERT...SELECT
.

BEGIN;
INSERT INTO base_class (data0, data1) SELECT d0, d1 FROM stuff
INSERT INTO derived_class (base_ids, data2, data3) SELECT ???, d2, d3 FROM stuff
COMMIT;


How am I supposed to tell MySQL to "link" every instance of
derived_class
to the previously created
base_class
through the base class IDs?

Ideally, I would "loop" through the two tables simultaneously, but that's impossible in MySQL or MariaDB:

# PSEUDOCODE! NOT VALID.
BEGIN;
INSERT INTO
base_class (data0, data1)
ALONG_WITH derived_class (base_ids, data2, data3)
SELECT d0, d1, GET_ALONG_FIELD(base_class, id), d2, d3 FROM stuff
# _______ table 1 values
# _______________________________________ table 2 values
COMMIT;


How can I solve this issue while maintaining the "hierarchical" design of my tables?




EDIT:

I've opened this question again because I'm curious if it's possible to achieve the behavior I desire without having to resort to procedural-like SQL code (using cursors).

Is there a way of solving this issue without using cursors?

Answer

Cursors should do the trick. You can loop through the stuff table and perform the inserts one at a time, grabbing the insert IDs as you go. Something like this (untested):

BEGIN;

  DECLARE get_stuff CURSOR FOR SELECT id FROM stuff;
  DECLARE current_id INT;

  OPEN get_stuff;
  insert_stuff: LOOP

    FETCH get_stuff INTO current_id;
    INSERT INTO base_class (data0, data1)
      SELECT d0, d1 FROM stuff WHERE id = current_id
    INSERT INTO derived_class (base_ids, data2, data3)
      SELECT mysql_insert_id(), d2, d3 FROM stuff WHERE id = current_id

  END LOOP insert_stuff;
  CLOSE get_stuff;

COMMIT;

Another possible approach would be to create a view on the two "insert" tables. There may be restrictions on inserting to the view based on how your tables are structured, but something like this might work (also not tested):

CREATE VIEW stuff_view AS
  SELECT b.data0, b.data1, d.data2, d.data3 FROM base_class AS b
  INNER JOIN dervied_class AS d ON (d.base_id = b.id)

INSERT INTO stuff_view (data0, data1, data2, data3)
  SELECT d0, d1, d2, d3 FROM stuff

I'm actually not sure if MySQL will automatically assign appropriate IDs to both the base and derived class tables.