Inserting two columns at the same time

Question

I am trying to insert into two connected tables.

Table 1: workouts(id, ...) The id auto increments.

Table 2: customWorkouts(id, workoutID, ....)

But the thing is, the two tables are connected, customWorkouts.workoutID references workouts.id.

My question is, how can I execute two consequtive queries, the first creating a new workout and the second creating a related customWorkouts? How can I make the created customWorkouts instantly connected to the workout that I just created?

Use `LAST_INSERT_ID()` in the second query to get the auto increment ID that was created in the first query. — Barmar, Mar 11 '20 at 23:32

GMB · Accepted Answer · 2020-03-11T23:37:06.417

0

Use last_insert_id():

insert into workouts(col1, col2) values('foo', 'bar');
insert into custom_workouts(workout_id, col1) values(last_insert_id(), 'baz');

Note that this requires you to run both queries on the same connection:

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It is not changed by another client.

edited Mar 11 '20 at 23:37

answered Mar 11 '20 at 23:33

GMB

216,147
25
84
135

@BassamKaddoura: yes, this is safe in a multi-connection environnement. See the quote from the documentation that I added to my answer. – GMB Mar 11 '20 at 23:37
Yeah, I just read the post Barmar linked, thank you! ^^ – Bassam Kaddoura Mar 11 '20 at 23:41

Inserting two columns at the same time

1 Answers1