CODEWITHSUNDEEP
mysqlsql
Wayne Stephens
Wayne Stephens

Reputation: 33

SQL code to change value of a field on one table based on value of a different field in a different table

I'm a complete novice at SQL so please be gentle...

I have a database of products. I would like to use SQL to move a batch of products to a different category, based on if a particular word is present in the products title, and also, only if the products quantity is zero.

The database includes 2 relevant tables:

So, for an example, a product with the following title (stored in the column products name):

Archimede Seguso Murano Incalmo Millefiori Glass Bowl

I would like the SQL code to find this product (any any others that contain the word Archimede in the title), check if the stock quantity is 0, and if so, change the value of the column master_categories_id to 277 for those products.

The code I have so far come up with is:

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

However, this does not work as the column products_name is not in the products table. I have spent many hours searching for the correct method of doing this, with no luck, and would be eternally grateful for any help!

My website is built using zencart, and the above SQL was run using Zen Carts Admin "SQL Query Executor", but I can also run the SQL using phpMyAdmin if that helps. I am using MySQL version 5.

Sorry if I have left out anything obvious that you need to know, like I said I am a complete novice, but I will try to provide any other info you need. Thanks in advance!

Upvotes: 3

Views: 1259

Answers (1)

Taryn
Taryn

Reputation: 247810

Assuming you have a products id column in both tables that you can use to JOIN, you will need to JOIN the tables to do the UPDATE:

UPDATE products p
INNER JOIN products_description pd
  on p.product_id = pd.product_id
SET p.master_categories_id = '277'
WHERE p.products_quantity = '0' 
  and pd.products_name = 'Archimede';

If you want to update any rows that contain Archimede, then you will have to use the LIKE operator in SQL: 

UPDATE products p
INNER JOIN products_description pd
  on p.product_id = pd.product_id
SET p.master_categories_id = '277'
WHERE p.products_quantity = '0' 
  and pd.products_name LIKE '%Archimede%';

The LIKE operator will find any rows that contain Archimede in the title.

Upvotes: 2

Related Questions