SitePoint Sponsor |
|
User Tag List
Results 1 to 7 of 7
-
Jun 16, 2008, 11:04 #1
- Join Date
- Dec 2004
- Location
- USA
- Posts
- 1,407
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
complicated INSERT - copy existing data while updating id fields
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!Last edited by WebDevGuy; Jun 16, 2008 at 11:59.
-
Jun 16, 2008, 11:43 #2
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
could you maybe use some other names? i think you've confused your tableas and fieldids
-
Jun 16, 2008, 11:58 #3
- Join Date
- Dec 2004
- Location
- USA
- Posts
- 1,407
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
)Last edited by WebDevGuy; Jun 16, 2008 at 12:30.
-
Jun 16, 2008, 14:56 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
each option belongs to only one product? that seems unusual
-
Jun 16, 2008, 16:54 #5
- Join Date
- Dec 2004
- Location
- USA
- Posts
- 1,407
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
that's the way this client has it
-
Jun 16, 2008, 18:53 #6
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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:Code: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 )
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
-
Jun 17, 2008, 05:31 #7
- Join Date
- Dec 2004
- Location
- USA
- Posts
- 1,407
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
perfect - many thanks!
Bookmarks