How to do MySQL Multi-insert from select statement

Question

So here are the basics of my question. I have selected a result set from the join of two tables. This result set is a series of id values and email addresses. Now for each row in this result set, I want to insert a new row into a third table called timestamps that has a timestamp from NOW() and the id value from the result set, but not the email address. I know I can achieve this by running a loop within a stored procedure. Additionally I think I cannot simply run an insert into statement because the data I am inserting into timestamps does not come from my result set in its entirety, my result set data only decides the number of rows that must be inserted and the ids, while I need to return those email addresses for other processing. I am searching for a clever way to avoid using a loop for this. Is that possible? Can I generate a dynamic insert statement? Thank you to anyone who gives time or help.

Please give example of your tables. please specify with examples what are your exact requirements. — Krishna Rani Sahoo
– Krishna Rani Sahoo, Commented Jul 22, 2013 at 9:04
@krishnasahoo Oh wow. I do not remember. I will review my notes and question and try to reconstruct what I was up to back then, but this is still an unsolved issue, so thank you for your time. — usumoio
– usumoio, Commented Jul 22, 2013 at 12:25

Ankit Gupta · Accepted Answer · 2013-09-18 19:08:40Z

1

Suppose:

table1 with columns: id

table2 with columns: id, email

table1 and table2 are linked with column id

timestamps with columns: id, time

Then query will be:

insert into timestamps(id,time) select table1.id,now() from table1 inner join table2 on table1.id = table2.id;

answered Sep 18, 2013 at 19:08

Ankit Gupta

18912 bronze badges

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

Collectives™ on Stack Overflow

How to do MySQL Multi-insert from select statement

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related