how to insert multiple rows in first table and insert in other table using foreign key by single query

Question

Due to the character limit, I couldn't choose a good title My topic is a bit more complicated but I will explain it simplified here.

i have 3 table

the entity table

| id | name
|----|------
|  1 |   X

the entity_color table

| id | entID (foreign key entity.id) | color
|----|-------------------------------|-------
| 1  |               1               |  blue
|----|-------------------------------|-------
| 2  |               1               |  red

the stock_room table

| id | entColID (foreign key entity_color.id) | quntity
|----|----------------------------------------|----------
| 1  |                    1                   |    10
|----|----------------------------------------|----------
| 2  |                    2                   |    20

How can I insert this 5 rows ( 1 rows into entity + 2 rows into entity_color + 2 rows into stock_room ) into a mysql database with a commited query?

I don't think there is a way, but it's worth asking. That's why I want to benefit from your like-mindedness

______________ UPDATE _______________

Unfortunately, some users may irresponsibly and without understanding the question saysaid that this question is repetitiverepeated, so I musthave to point out thatthis point:

if we ignore the third table, this is a solution

START TRANSACTION;
    INSERT INTO `entity` (`name`) VALUES (`x`);
    INSERT INTO `entity_color` (`entID`,`color`) VALUES (last_insert_id(),'blue'),(last_insert_id(),'red');
COMMIT;

but i have 3 table, it is not posibble that get two ids from entity_color with last_insert_id()

Why do you try to do this with one query? Whats wrong in using multiple queries? — Progman, Apr 10 '23 at 13:19
There is no problem in calling `LAST_INSERT_IN()` multiple times for the different `INSERT INTO` queries and use the generated Ids in the other queries. — Progman, Apr 10 '23 at 13:42
You can run the `INSERT INTO` queries for each individual row and get the created ID with `LAST_INSERT_ID()`. Then you use this value for the next `INSERT INTO` query. The `LAST_INSERT_ID()` function is indeed not helpful for `INSERT INTO` queries which inserts multiple rows. — Progman, Apr 10 '23 at 13:56

score 0 · Answer 1 · answered Apr 10 '23 at 13:21

The INSERT statement can insert only to one table. You will need multiple INSERT statements.

You could use triggers to insert into other tables, but you can't pass the column values.

You can run multiple statements in the context of a transaction to make sure they all get committed.

how to insert multiple rows in first table and insert in other table using foreign key by single query

1 Answers1