Yohan Hirimuthugoda Yohan Hirimuthugoda - 4 years ago 143
SQL Question

MySQL INSERT INTO query column value equals another SQL SELECT query

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?

Answer Source

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'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download