Insert generated key from one table into second table with batch in one request

Question

There are two tables with the dependence one to many on field occupation_id (project about booking hotel rooms). Each occupation entity can have several booked rooms. Is there way to do in a single sql request insertion to the first table (occupation) and several batch insertions to the second table(booked_rooms).

Tables are:

Table: Occupations
occupation_id bigint (autoincrement)
user_id bigint
check_in_date date
check_out_date date
status text

Table: Booked_rooms
booked_room_id bigint (autoincrement)
occupation_id bigint
beds int
class_rate int

You cannot update several tables in a single query. However you can execute several queries in the same database transaction and commit changes all together... Or you can use triggers to automate changes on related tables — GMB, Dec 29 '18 at 17:29

score 2 · Accepted Answer · answered Dec 29 '18 at 17:26

2

Use a transaction:

BEGIN;
INSERT INTO ...;
SELECT LAST_INSERT_ID();
INSERT INTO ...;
COMMIT;

answered Dec 29 '18 at 17:26

Rick James

135,179
13
127
222

Insert generated key from one table into second table with batch in one request

1 Answers1