title: How to update two tables in one mySQL query
body: <p>I have the following query in mysql.</p>
  <pre><code>UPDATE products A 
  JOIN ea_pid B ON A.product_id = B.vpid
  JOIN (SELECT product_id, sum(quantity) as quantity2 from ea_products 
  GROUP BY product_id) D ON A.product_id = D.product_id
  SET A.p_in_stock = (case when B.id = '1' THEN '30' when B.id = '0' THEN D.quantity2 end ) WHERE 1
  </code></pre>
  <p>I would like to update a second table with the same numbers. I tried to follow this solution <a href="https://stackoverflow.com/questions/4361774/mysql-update-multiple-tables-with-one-query">MySQL, update multiple tables with one query</a> however always receive an error</p>
  <blockquote>
  <h1>1054 - Unknown column 'A.product_id' in 'on clause'</h1>
  </blockquote>
  <p>This is the modified query I have so far which is producing this error:</p>
  <pre><code>UPDATE products_1 A, products_2 E 
  JOIN ea_pid B ON A.product_id = B.vpid
  JOIN (SELECT product_id, sum(quantity) as quantity2 from ea_products 
  GROUP BY product_id) D ON A.product_id = D.product_id
  SET A.p_in_stock = (case when B.id = '1' THEN '30' when B.id = '0' THEN D.quantity2 end ), E.p_in_stock = (case when B.id = '1' THEN '30' when B.id = '0' THEN D.quantity2 end ) WHERE 1
  </code></pre>
  <p>I would appreciate a lot any help how I could solve this</p>