MySQL - INSERT INTO SELECT ( INSERT INTO ...) - Nested insert into's

Question

I'm trying to insert two rows on different tables at the same time.
The value of the subquery INSERT INTO's AUTO_INCREMENT or id (if it already exists) should be written in the main query.

Currently I have this (simplified with just 3 values), but its not working. I'm wondering if there is a better way to do this.

INSERT IGNORE INTO access(`entryid`, `logid`, `urlid`)  
   SELECT '0', '1', (INSERT IGNORE INTO urls(`url`) VALUES('example.com'));

I can obviously do multiple separate queries for this purpose, but I think inserting it into one query improves reliability of my code (as it can never "fail" half-way through its queries). I'm just looking for a nested INSERT INTO solution.

xolympus · Accepted Answer · 2020-01-23 15:29:27Z

1

Why not move those queries into a transaction? If you do that if any of those queries fail the whole block will get rolled back. If you use LAST_INSERT_ID() you can get the previous inserted ID and use that (see docs).

Example based on your query:

START TRANSACTION;
INSERT IGNORE INTO urls(`url`) VALUES('example.com');
INSERT IGNORE INTO access(`entryid`, `logid`, `urlid`) VALUES (0, 1, LAST_INSERT_ID());
COMMIT;

answered Jan 23, 2020 at 15:29

xolympus

501 silver badge4 bronze badges

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

1 Comment

xolympus Over a year ago

OP said that he wants to make sure the queries cannot "fail" half-way through, so therefore the transaction.

Collectives™ on Stack Overflow

MySQL - INSERT INTO SELECT ( INSERT INTO ...) - Nested insert into's

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related