0

i have 3 tables that i want insert in these with single query. These tables have more columns, but here I explain them very simple. i need a single query that insert this records in those tables.

the first table is entities

id name
1 ABC

the second table is entities_pattern

id entID patID (id of pattern table)
1 1 100
2 1 101

and the third table is stock_room

id entPatID inventory
1 1 0
2 2 0

if we ignore the third table, we can use this query.

START TRANSACTION;
INSERT INTO
    `entities` (`name`)
    VALUES ('ABC');
SET @rowCount = ROW_COUNT();
SET @lastID = LAST_INSERT_ID();
INSERT INTO
    `entities_pattern` (`entID`, `patID`)
    VALUES
        (@lastID, '100'),
        (@lastID, '101');
COMMIT;

but I don't know how to add the third table to this query. it may be possible to use this query.

START TRANSACTION;
INSERT INTO
    `entities` (`name`)
    VALUES ('ABC');
SET @rowCount = ROW_COUNT();
SET @lastID = LAST_INSERT_ID();
INSERT INTO
    `entities_pattern` (`entID`, `patID`)
    VALUES
        (@lastID, '100'),
        (@lastID, '101');
SET @rowCount = ROW_COUNT();
SET @lastID = LAST_INSERT_ID();
INSERT INTO
    `stock_room` (`entPatID` , `inventory`)
    VALUES
        (@lastID, '0'),
        (@lastID+1, '0');
COMMIT;

i have 2 questions now:

1- is this query the best way to do it?

2- what will happen if multiple users want to enter something in these tables at the same time? Can we be sure that @lastID +1, gives us the true entPatID?

babak-maziar
  • 23
  • 7
  • OK, so don't try to guess the id's of the records you inserted to the entities_pattern table. You need to get the actual id's. One way to do that is to query the entities_pattern table using the last_id and get the id's of those new rows. – devlin carnate Feb 21 '23 at 19:49
  • Thanks, but my emphasis in this question is to insert all three records with single query and I don't want it to be done in two steps. – babak-maziar Feb 21 '23 at 20:06
  • see [here](https://stackoverflow.com/questions/19895597/insert-multiple-rows-parent-lastinsertid) – devlin carnate Feb 21 '23 at 20:25
  • in this question we have 3 table, for 2 table i write the solotion in my question – babak-maziar Feb 22 '23 at 09:14

0 Answers0