MySQL insert once for each subquery result

Question

I want to execute a MYSQL query:

INSERT INTO taskattempts (taskID) VALUES (_);

which I want to execute multiple times, once for each result of the query:

SELECT id FROM tasks WHERE stageID = 1;

using the resultant id from the second query as the value of taskID in the first.

Is there any way to do this using a single MySQL query? A PHP loop running multiple queries seems dreadfully inefficient.

*EDIT *

I actually need to fill in multiple columns, two static and one with the subquery. I found that INSERT INTO taskattempts (stageAttemptID, taskID, complete) SELECT 0, id, 5 FROM tasks WHERE stageID = 5; works, but wonder whether this is kosher. Also, in the interest of knowledge, can you fill in different columns with different queries?

Larry Lustig · Accepted Answer · 2011-09-30 12:11:23Z

4

 INSERT INTO taskattempts (taskID) SELECT id FROM tasks WHERE stageID = 1;

(This is universally supported standard SQL).

In response to the second part of the question, you can freely mix constants and columns in the SELECT clause:

 INSERT INTO taskattempts (stateAttemptID, taskID, complete) 
    SELECT 0, id, 5 FROM tasks WHERE stageID = 1;

If your constants are strings, don't forget to quote them. And remember, you can include a complex SELECT statement with JOINs and aggregation if required.

edited Sep 30, 2011 at 12:11

answered Sep 30, 2011 at 3:15

Larry Lustig

51.2k16 gold badges119 silver badges173 bronze badges

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

2 Comments

wyatt Over a year ago

Well that was simple. Though, in an effort to simplify my question, I've missed something. I actually need to fill in multiple columns, two static and one with the subquery. I found that INSERT INTO taskattempts (stageAttemptID, taskID, complete) SELECT 0, id, 5 FROM tasks WHERE stageID = 5; works, but wonder whether this is kosher. Also, in the interest of knowledge, can you fill in different columns with different queries?

rahularyansharma Over a year ago

@wyatt if u miss something in your question edit the question . dont use comments for extending the question.

Collectives™ on Stack Overflow

MySQL insert once for each subquery result

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related