1

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?

Community
  • 1
  • 1
Vittorio Romeo
  • 90,666
  • 33
  • 258
  • 416
  • This could be a lot easier with a scripting language external to MySQL. Python, Ruby, or even NodeJS could easily handle this recursion. – tadman Feb 20 '15 at 20:12
  • @tadman: True, the easiest solution would be using an external language. I'm, however, really curious if this can be implemented in pure MySQL – Vittorio Romeo Feb 20 '15 at 20:28

2 Answers2

2

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.

Brandon Gano
  • 6,430
  • 1
  • 25
  • 25
0

I'm not entirely sure I understand your question. I think you're trying to insert d0 and d1 into base_class and base_ids, d2, and d3 into derived_class in the same order so that the nth inserted values in both base_class and derived_class all come from the nth record in stuff. If my understanding is correct, the following transaction will do what you want.

BEGIN;
INSERT INTO base_class (data0, data1)
SELECT d0, d1
FROM stuff
ORDER BY base_ids
INSERT INTO derived_class (base_ids, data2, data3)
SELECT base_ids, d2, d3
FROM stuff
ORDER BY base_ids
COMMIT;
  • `base_ids` is not a column in the `stuff` table. Sorry if my explanation was confusing. I'm trying to say that I have a hierarchy of objects (think in OOP terms) - `derived_class` rows are instances that derive from `base_class` rows. If I need to create an instance of `derived_class`, I'm forced by a foreign key constraint to create a `base_class` instance first and then store its ID in the `derived_class` instance that will be created afterwards. Easy when I have to create one because I can use `LAST_INSERT_ID()`. If I have to create more instances I have no way of getting all the new IDs. – Vittorio Romeo Feb 20 '15 at 20:31