1

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.

Asterdahl
  • 85
  • 6
  • possible duplicate: http://stackoverflow.com/questions/7990227/can-you-do-a-for-each-row-loop-using-mysql – jchapa Dec 04 '12 at 20:23

1 Answers1

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
PinnyM
  • 35,165
  • 3
  • 73
  • 81
  • 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. – Asterdahl Dec 04 '12 at 22:49