title: SQL code to change value of a field on one table based on value of a different field in a different table
body: <p>I'm a complete novice at SQL so please be gentle...</p>
  
  <p>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.</p>
  
  <p>The database includes 2 relevant tables:</p>
  
  <ul>
  <li><p>1st table is <code>products</code> which has these 2 relevant columns: <code>products_quantity</code> and <code>master_categories_id</code></p></li>
  <li><p>2nd table is <code>products_description</code> and it has relevant column <code>products_name</code> (the title)</p></li>
  </ul>
  
  <p>So, for an example, a product with the following title (stored in the column <code>products name</code>):</p>
  
  <pre><code>Archimede Seguso Murano Incalmo Millefiori Glass Bowl
  </code></pre>
  
  <p>I would like the SQL code to find this product (any any others that contain the word <code>Archimede</code> in the title), check if the stock quantity is <code>0</code>, and if so, change the value of the column <code>master_categories_id</code> to <code>277</code> for those products.</p>
  
  <p>The code I have so far come up with is:</p>
  
  <pre><code>UPDATE products
  SET master_categories_id = '277'
  WHERE products_quantity = '0' and products_name = 'Archimede';
  </code></pre>
  
  <p>However, this does not work as the column <code>products_name</code> is not in the <code>products</code> 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!</p>
  
  <p>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.</p>
  
  <p>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!</p>