MySQL INSERT INTO query column value equals another SQL SELECT query

Question

Could anybody kindly guide me on correct direction for below query? It's not working under phpMyAdmin.

INSERT INTO `Setting` 

(`id`, `type`, `name`, `value`, `parentId`, `createdAt`, `updatedAt`, `createdById`, `updatedById`) 

VALUES 

(NULL, 0, 'howItWorks', 'Some URL', NULL, NULL, NULL, -1, NULL), 

(NULL, 0, 'howItWorksThumb', 'Some URL', (SELECT id FROM Setting WHERE name = 'howItWorks'), NULL, NULL, -1, NULL);

Same kind of query works under PostgreSQL.

Error I am getting: #1093 - You can't specify target table 'Setting' for update in FROM clause

Question is update to explained issues related to LAST_INSERT_ID() solutions:

To use LAST_INSERT_ID() solutions; child row should be inserting immediately after parent row.

I want to get the parentId for child row not immediately after I insert parent row. What will be the solution?

Also what if I want to add two children for same parent row?

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

Answer 1

MySQL doesn't allow you to SELECT in a subquery from the same table that you're inserting into in the main query. So you'll need to split this into two INSERT queries:

You can use LAST_INSERT_ID() to get the auto-increment ID that was assigned in the last INSERT, rather than using a subquery.

INSERT INTO `Setting` (`id`, `type`, `name`, `value`, `parentId`, `createdAt`, `updatedAt`, `createdById`, `updatedById`) 
    VALUES (NULL, 0, 'howItWorks', 'Some URL', NULL, NULL, NULL, -1, NULL);
INSERT INTO `Setting` (`id`, `type`, `name`, `value`, `parentId`, `createdAt`, `updatedAt`, `createdById`, `updatedById`) 
    VALUES (NULL, 0, 'howItWorksThumb', 'Some URL', LAST_INSERT_ID(), NULL, NULL, -1, NULL);

Unfortunately, using LAST_INSERT_ID() still doesn't allow you to combine them into a single query, because it calls the function before doing any inserts.

If you're doing the second insert later, you can do it with a normal INSERT ... SELECT ...:

INSERT INTO `Setting` (`id`, `type`, `name`, `value`, `parentId`, `createdAt`, `updatedAt`, `createdById`, `updatedById`) 
SELECT NULL, 0, 'howItWorksThumb', 'Some URL', id, NULL, NULL, -1, NULL
FROM Setting
WHERE name = 'howItWorks'

MySQL INSERT INTO query column value equals another SQL SELECT query

Sign up