complicated INSERT - copy existing data while updating id fields

[WebDevGuy]

I have two tables:

products:
product_id
vendor_id

options:
option_id
product_id
option_name

I want to duplicate some records in options with a new product_id.

The following query gets me the records I want to duplicate:
SELECT *
FROM `options`
WHERE `product_id`
IN (
SELECT `product_id`
FROM `products`
WHERE `vendor_id` =200
)

How can I automate the insert without exporting to spreadsheet, rewriting every record by hand,...?

Many thanks for any help!

[r937]

could you maybe use some other names? i think you've confused your tableas and fieldids

[WebDevGuy]

This should help, hopefully.<I also edited the original post for clarity of other repliers.>

I want to copy a store's list of products and product options to a new (like a franchise) store, leaving the existing store as is. I have copied all the products, that was an easy changed. However, copying the product options is not, as far as I can see, as easy chore.

products:
product_id
vendor_id

options:
option_id
product_id
option_name

The following query gets me the records I want to duplicate:
SELECT *
FROM `options`
WHERE `product_id`
IN (
SELECT `product_id`
FROM `products`
WHERE `vendor_id` =200
)

[r937]

each option belongs to only one product? that seems unusual

[WebDevGuy]

that's the way this client has it

[r937]

so let me get this straight, if the products were cars, you could have the following situation:

products
11 ford
12 chev
13 lada

options
104 11 power steering
105 12 power steering

repetition of the option name many times (once for each product that has the option) is likely suboptimal

oh well

anyhow, do the insert like this:

INSERT
  INTO options
     ( option_id
     , product_id
     , option_name )
SELECT NULL
     , product_id
     , option_name
  FROM options
 WHERE product_id IN 
       ( SELECT product_id
           FROM products
          WHERE vendor_id = 200 )

this assumes option_id is an auto_increment

if it isn't, you'll ahve to come up with some other way to assign the new values to it for the rows being inserted

[WebDevGuy]

perfect - many thanks!