Mysql use last inserted id in a different table query multiple times

Question

I am working on a query that I'd like to run to import some phrases based on actions.

These are the two tables I want to insert data into.

actions:
 - id
 - name
 - data

phrases:
 - id
 - action_id
 - phrase

Where phrases.action_id = actions.id

I want to create a new action. Return that ID and then add multiple phrases using the returned ID.

Is there anyway of making the action.id that was inserted persistent or a variable for re-use.

My train of thinking has led me to things like:

SELECT LAST_INSERT_ID();

And

OUTPUT insterted.id

Not expecting an answer but some helpful information that will point me in the right direction would be great

score 2 · Accepted Answer · answered May 17 '17 at 22:08

You can assign it to a user variable:

INSERT INTO actions ...;
SET @action_id = LAST_INSERT_ID();

Then you can use @action_id in all the INSERT queries that insert into phrases.

INSERT INTO phrases (action_id, phrase) VALUES (@action_id, "Whatever");
INSERT INTO phrases (action_id, phrase) VALUES (@action_id, "Some other phrase");

You could also solve it by doing all the inserts in a single query:

INSERT INTO phrases (action_id, phrase) VALUES
    (LAST_INSERT_ID(), "Whatever"),
    (LAST_INSERT_ID(), "Some other phrase");

Mysql use last inserted id in a different table query multiple times

1 Answers1