How to insert to two tables with one query if one table is a junction table?

Question

How can I INSERT values to two tables using only one query? I am using MySQL. One of the tables I want to insert to is a many-to-many relationship table. Please see my example below:

I recently added the many-to-many relationship tables. When I insert on the news, I type the following script:

INSERT INTO news (title, reporter_id) 
VALUES ('Some Title', 15);

How can I have one query an be able to insert to two tables? Per MySQL insert documentation, seems like I can do query like

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

The problem is, I dont know my news_id until I execute my first insert. Should I just have two insert statements or is there a better way? Thanks for your help!

`START TRANSACTION; INSERT INTO first; INSERT INTO SECOND(LAST_INSERT_ID()); END TRANSACTION;` — Mihai, Sep 26 '17 at 19:16
That syntax is for inserting multiple rows into a single table; not inserting into multiple tables. There is no syntax that I am aware of for doing that, aside from transactions, but those aren't really single queries. — Uueerdo, Sep 26 '17 at 19:16
Possible duplicate of [sql - insert into multiple tables in one query](https://stackoverflow.com/questions/3860280/sql-insert-into-multiple-tables-in-one-query) — Vikas Kumar, Sep 26 '17 at 19:16
Also, what is the intended purpose of the `junctions` table? With the data shown, it looks redundant; but could be populated with an AFTER INSERT **trigger** on the `news` table. — Uueerdo, Sep 26 '17 at 19:20
@Uueerdo Here is my complete query: START TRANSACTION; INSERT INTO news (title, reporter_id) VALUES ('Some Title', 22); INSERT INTO junctions (reporter_id, news_id) VALUES (22, LAST_INSERT_ID()); END TRANSACTION; I get an error of error near near 'END TRANSACTION' at line 3. I tried several ways to debug. Do you know which error is it referring to? It seems to run the first insert transaction.. — Marvin, Sep 26 '17 at 19:35

score 0 · Answer 1 · answered Sep 26 '17 at 20:38

As mentioned by Uueerdo in the comments, you can use an AFTER INSERT trigger and access the generated ID with NEW.id. However - if you want to keep that logic in your application layer, you can copy the data from the first table to the second after your bulk insert with:

start transaction;

insert into news(title, reporter_id) values
    ('title2', 2),
    ('title3', 3);

insert into junctions(news_id, reporter_id)
    select id, reporter_id
    from news
    where id >= last_insert_id()
    order by id asc
    limit 2;

commit;

This works for InnoDB if the innodb_autoinc_lock_mode is set to 0 or 1, because the generated IDs are guaranteed to be consecutive.

With innodb_autoinc_lock_mode set to 0 (“traditional”) or 1 (“consecutive”), the auto-increment values generated by any given statement are consecutive, without gaps, because the table-level AUTO-INC lock is held until the end of the statement, and only one such statement can execute at a time.

AUTO_INCREMENT Handling in InnoDB

LAST_INSERT_ID() will return the generated ID of the first inserted row.

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only.

Information Functions - LAST_INSERT_ID()

You know the first generated ID. You know the number of inserted rows. So you know which rows to copy.

Demo: http://rextester.com/UEN69961

How to insert to two tables with one query if one table is a junction table?

1 Answers1