June 24th, 2013, 04:23 PM
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.
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.
June 24th, 2013, 06:50 PM
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
June 25th, 2013, 05:21 AM
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
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')
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?