mysqlsqldatabasesql-insertlast-insert-id

Inserting two columns at the same time

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?

Solution

  • 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.