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
– GMB, Commented Dec 29, 2018 at 17:29

Rick James · Accepted Answer · 2018-12-29 17:26:54Z

2

Use a transaction:

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

answered Dec 29, 2018 at 17:26

Rick James

144k15 gold badges144 silver badges254 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related