INSERT RETURNING from one query to another

February 21, 2022

So I need to insert a value into two tables but I need the id to be the same in both tables, I am trying to get the retuning ID from the first query to pass it to the second one.
This is what I have so far.

INSERT
    INTO
    FantasyLeague.calendar (
  round,
    status,
    race_initial,
    race_name,
    city,
    country,
    circuit_name,
    start_date,
    quali_deadline,
    sprit_deadline,
    race_deadline,
    series_id)
VALUES (
new_round,
new_status,
new_race_initial,
new_race_name,
new_city,
new_country,
new_circuit_name,
new_start_date,
new_quali_deadline,
new_sprit_deadline,
new_race_deadline,
new_series_id)
RETURNING SET @id = race_id;

INSERT
    INTO
    FantasyLeague.races (race_id,
    drivers_results,
    series_id)
VALUES(
    @id,
    new_driver_results,
    new_series_id
  );

race_id is an auto-incrementing INT in FantasyLeague.calendar but not in FantasyLeague.races.
I tried using the approach from this post but I get a syntax error.

INSERT
    INTO
    FantasyLeague.calendar (
    round,
    status,
    race_initial,
    race_name,
    city,
    country,
    circuit_name,
    start_date,
    quali_deadline,
    sprit_deadline,
    race_deadline,
    series_id)
VALUES (
new_round,
new_status,
new_race_initial,
new_race_name,
new_city,
new_country,
new_circuit_name,
new_start_date,
new_quali_deadline,
new_sprit_deadline,
new_race_deadline,
new_series_id)
RETURNING race_id) )

INSERT
    INTO
    FantasyLeague.races (race_id,
    drivers_results,
    series_id)
SELECT 
    race_id,
    new_driver_results,
    new_series_id;

Thanks in advance.

>Solution :

Never used Maria but I believe you’d adopt the MySQL approach:

INSERT INTO FantasyLeague.races (
    race_id,
    drivers_results,
    series_id)
VALUES(
    LAST_INSERT_ID(),
    new_driver_results,
    new_series_id
);

LAST_INSERT_ID() returns the ID that was just before inserted into FantasyLeague.calendar, thereby wiring up the relationship

You could also stash the ID in a variable that you use (potentially multiple times)..

INSERT INTO calendar ...;

SET @lii = LAST_INSERT_ID();

INSERT INTO FantasyLeague.races (
    race_id,
    drivers_results,
    series_id
)
VALUES(
    @lii,
    new_driver_results,
    new_series_id
);

INSERT INTO FantasyLeague.other(
    race_id,
    blah
)
VALUES(
    @lii,
    blah
);