SQL to change "master_categories_id" value based on "products_name"

I'd like to be able to use SQL code to search for products containing a certain word in the title (products_name) and then reset the products master_categories_id of those products to a specific number, and only if the products_quantity is zero.

I know nothing about SQL, but I've done some Googling and come up with this:

UPDATE products
SET master_categories_id = '277'
WHERE products_quantity = '0' and products_name = 'Archimede';

So in this example I would hope that all products that have the word "Archimede" in the title and have a quantity of zero, will have their master category id set to "277".

Of course, it doesn't work as the "products_name" field is in the "products_description" table, not the "products" table, and after spending a long time searching, I can't figure out how to correct this. Please help! :smile:

It's a little more complicated when multiple tables are involved. To do that, must use multiple table names and add criteria to properly join the two tables on a common piece of information, etc.
Plus, you wrote products_name = 'Archimede' when you wanted it to 'affect products that have the word "Archimede" in the title', but your use of "=" checks for an exact match. Instead, you'd need to use a matching syntax, where '%' is the matching rule (much like a '*' would be used in other systems).

So, something like this would work better:

**BUT I STRONGLY RECOMMEND YOU DO NOT DO THIS!
**

update products p, products_description pd 
SET master_categories_id = 277
WHERE p.products_id = pd.products_id
AND products_quantity = 0 
AND products_name LIKE  '%Archimede%';

NOTE: THIS IS NOT NECESSARILY A COMPLETE HANDLING OF master categories though!!!!!
There's more to it than just these pieces.
Especially with v1.3.8a which is missing some safeties to recover from incorrectly mangled data.

ALWAYS DO A COMPLETE DATABASE BACKUP BEFORE RUNNING ANY SQL STATEMENTS AGAINST IT!!!!
ALWAYS DO A COMPLETE DATABASE BACKUP BEFORE RUNNING ANY SQL STATEMENTS AGAINST IT!!!!
ALWAYS DO A COMPLETE DATABASE BACKUP BEFORE RUNNING ANY SQL STATEMENTS AGAINST IT!!!!
ALWAYS DO A COMPLETE DATABASE BACKUP BEFORE RUNNING ANY SQL STATEMENTS AGAINST IT!!!!

oh, and in case I didn't emphasize it enough ... remember to ALWAYS DO A COMPLETE DATABASE BACKUP BEFORE RUNNING ANY SQL STATEMENTS AGAINST IT!!!!

NOTE: if the Product is not already in the Category for the master_categories_id you need to fix the products_to_categories table ... both to remove/add to that table as needed ...

Agreed.

There's a lot more to altering categories than just randomly flipping the number in one table.