SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Zealot samohtwerdna's Avatar
    Join Date
    Jul 2007
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    insert into help with condition

    hello,

    I have a table that stores related products. it is a simple table with 2 fields. the first is a product_id field and the second is an array of product_id's that are related to the product_id in the first field.

    I would like to write a 'insert into' that will query my main product table and find all the products that have a product_parent_id (child products) that matches a product_id from my related products table in the first field - and then adds a new record for each child product that also copies the array of product_id's from the second field in the related products table.

    some thing like:
    product tbl

    product_id | product_parent_id | product_name
    2 | 0 | Color Reflector
    3 | 2 | Color Ref 01
    4 | 2 | Color Ref 01
    5 | 2 | Color Ref 01

    related products tbl

    product_id | product_relations
    2 | 12,22,24,33,49


    And what I would want my 'insert into' to accomplish is to add product_id's 3-5 to the related products table with the same product relations as their parent products, like:

    related products tbl updated

    product_id | product_relations
    2 | 12,22,24,33,49
    3 | 12,22,24,33,49
    4 | 12,22,24,33,49
    5 | 12,22,24,33,49

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    is it too late to ask you to change the design of the tables?

    any time you store a comma-separated list of ids in a single column, you're asking for a world of hurt
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot samohtwerdna's Avatar
    Join Date
    Jul 2007
    Posts
    151
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    yes, it is to late - but the good news is that I am not actually comma separating the values but | is my separator. So visually I thought the multiple bars would be confusing in the post. Sorry about that. I was going to use a table in the post but I could find that as a tool anymore.

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    this is not going to be easy to accomplish. you'll have to do it in your host language.
    Check out our new Industry News forum!
    Keep up-to-date with the latest SP news in the Community Crier

    I edit the SitePoint Podcast

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,276
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by samohtwerdna View Post
    I am not actually comma separating the values but | is my separator.
    the separator does not matter, it is the multiple values in a single column that is causing you difficulty

    your queries will require a table scan and your app will not scale (slower and slower the more rows in the table)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •