UPDATE with complicated WHERE condition

[ganesch]

I spend a couple of hours to figure this out, but my SQL is quite poor

I have to update about 300 products in my products table. Those products belong to certain categories which are defined in another table called products_to_categories.

My query should look something like this:

UPDATE products SET products_tax_class_id = 0 WHERE products_id = x

... where x are all products belonging to a certain category.

Is there something like an UPDATE ... SELECT command in MySQL or can I put a JOIN in my WHERE condition in order to filter only those products belonging to a certain category?

Any help would be really appreciated
Nick

[r937]

yes you can do this, it's the "multi-table" delete syntax, and it's in the fine manual, along with the remark that it only works in 4.0.something (see the manual for the something)

[ganesch]

Multiple-table syntax:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_definition]

... but I wanted to UPDATE not DELETE

[r937]

did i say delete?

oh, i am so sorry

i meant to say that the multi-table update syntax is in the fine manual

[ganesch]

Database: MySQL 3.23.36

[r937]

heh, good answer

you'll need two queries

the first one finds the product_ids by category

select products_id 
  from products_to_categories
 where categories_id = number

then you construct a comma-delimited list of the product_ids

(in php i think you implode them or something, in coldfusion it's the ValueList function)

then run your upodate:

update products 
   set products_tax_class_id = 0 
 where products_id IN ( list of ids ) 

[ganesch]

Wonderful

Thank you so much