CODEWITHSUNDEEP
mysqlsqlsubquery
anon
anon

Reputation:

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

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.

Upvotes: 0

Views: 154

Answers (1)

xolympus
xolympus

Reputation: 50

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;

Upvotes: 1

Related Questions