Thread: Query help

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0

    Angry Query help


    I have a table (p) which holds numeric ids of products, another table (pc) which links the products to numeric ids of categories that the products are held within. One product can be linked to many categories, i.e. product 123 might have 5 records in pc

    pid catid default
    123 9 0
    123 10 0
    123 11 1
    123 12 0
    123 99 0


    In this table (pc) there is a field (default category) this is set to 1 for the main category for a product and all other records for the product in pc are set to 0.

    I need an update query to ensure that all products in pc have a record with the default category field set to 1, currently some have all records default category fields set to 0.

    i.e.

    pid catid default
    234 12 0
    234 13 0
    234 99 0


    I also need an extension to the query...
    If a product is in a specific category, I need to set this to be the only default record e.g.

    in the above examples I need products 123 and 234 to both have the catid 99 record to be set to default (1).
    For products that do not have a record for catid 99, they just need one of their records with the default field set to 1 (does not matter which)

    I just cannot get my head around where to start with this one.
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,027
    Rep Power
    4210
    first of all, you cannot do this with one query, because mysql will not let you update a table while selecting from that table in a subquery

    so to find out which rows you will end up updating, you need a query to identify them, and then save the keys in some (temporary or otherwise) table, then run the update query with those keys

    make sense?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2013
    Posts
    2
    Rep Power
    0
    Thanks for your clarification. I am from a 4GL / Progress background and that would be the approach I would need to take in that, but I was not sure if there was a direct way with MYSQL / PHP. I have come accross the (not exists) extension to a query with an insert
    i.e.
    INSERT INTO `table` (value1, value2)
    SELECT 'stuff for value1', 'stuff for value2' FROM `table`
    WHERE NOT EXISTS (SELECT * FROM `table`
    WHERE value1='stuff for value1' AND value2='stuff for value2')
    LIMIT 1

    But I could not get my head around designing this for the update.
    I had also looked at a query similar to

    IF (SELECT COUNT(*) FROM pc WHERE pid = 123 and default = 1)
    UPDATE pc SET default=....

    But I assume I will need the temp table to avoid a select and update combination?

IMN logo majestic logo threadwatch logo seochat tools logo