SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard
    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.

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    could you maybe use some other names? i think you've confused your tableas and fieldids
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    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.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    each option belongs to only one product? that seems unusual
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    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

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,262
    Mentioned
    60 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 )
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Dec 2004
    Location
    USA
    Posts
    1,407
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    perfect - many thanks!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •