Replicating a "For Each" loop in a MySQL query

Question

I've been using MySQL at work, but I'm still a bit of a noob at more advanced queries, and often find myself writing lengthy queries that I feel (or hope) could be significantly shortened.

I recently ran into a situation where I need to create X number of new entries in a table for each entry in another table. I also need to copy a value from each row in the second table into each row I'm inserting into the first.

To be clear, here's pseudocode for what I'm attempting to do:

For each row in APPS
   create new row in TOKENS
   set (CURRENT)TOKENS.APP_ID = (CURRENT)APPS.APP_ID

Any help is appreciated, even if it boils down to "this isn't possible."

As a note, the tables only share this one field, and I'll be setting other fields statically or via other methods, so simply copying isn't really an option.

possible duplicate: stackoverflow.com/questions/7990227/…

jchapa
– jchapa

2012-12-04 20:23:12 +00:00
Commented Dec 4, 2012 at 20:23 — jchapa
– jchapa, Commented Dec 4, 2012 at 20:23

PinnyM · Accepted Answer · 2012-12-04 20:26:30Z

3

You don't need a loop, you can use a single INSERT command to insert all rows at once:

INSERT INTO TOKENS (APP_ID)
SELECT APP_ID 
FROM APPS;

If you want to set other values for that row, simply modify the INSERT list and SELECT clause. For example:

INSERT INTO TOKENS (APP_ID, static_value, calculated_value)
SELECT APP_ID, 'something', 'calculated-' + APP_ID
FROM APPS

answered Dec 4, 2012 at 20:26

PinnyM

35.6k4 gold badges77 silver badges84 bronze badges

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

1 Comment

Asterdahl Over a year ago

Thanks this answers my question! I feel a little silly for not seeing this solution. This is sort of a spin off question in response to your answer, but how can I increment an integer in that SELECT statement? I understand how to insert a static or calculated value in a select statement. Unfortunately, the primary key is a BIGINT not AutoIncrement and I have no power to change that.

Collectives™ on Stack Overflow

Replicating a "For Each" loop in a MySQL query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related