How to update two tables in one mySQL query

Question

I have the following query in mysql.

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

I would like to update a second table with the same numbers. I tried to follow this solution MySQL, update multiple tables with one query however always receive an error

1054 - Unknown column 'A.product_id' in 'on clause'

This is the modified query I have so far which is producing this error:

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

I would appreciate a lot any help how I could solve this

The error message means that the products table does not have a product_id field. — Shadow, Mar 14 '17 at 19:12
Thank you but this is quite not possible. The second table is a copy of the first one only with different numbers in p_in_stock field. I can run the query seperately for both tables but not together — user3358102, Mar 14 '17 at 19:27
You have no joining condition for the `E` table. So this is going to update every row in that table. — Barmar, Mar 14 '17 at 19:47
Don't mix a cross product with explicit `JOIN` clauses. Use `JOIN` for everything. — Barmar, Mar 14 '17 at 19:47

Barmar · Accepted Answer · 2017-03-14T20:34:14.133

1

Don't mix cross products with explicit JOIN clauses. I think it doesn't recognize A in the ON clause because of this. You also need a joining condition for products_2, otherwise it will update all rows with the same values.

UPDATE products_1 AS a
JOIN products_2 AS e ON a.product_id = e.product_id
JOIN ea_pid AS b ON a.product_id = b.vpid
JOIN (SELECT product_id, SUM(quantity) AS quantity2
      FROM ea_products
      GROUP BY product_id) AS d
    ON a.product_id = d.product_id
SET a.p_in_stock = (CASE b.id
                    WHEN '1' THEN '30'
                    WHEN '0' THEN d.quantity2
                    END),
    e.p_in_stock = (CASE b.id
                    WHEN '1' THEN '30'
                    WHEN '0' THEN d.quantity2
                    END)

edited Mar 14 '17 at 20:34

answered Mar 14 '17 at 19:53

Barmar

741,623
53
500
612

Thank you Barmar. This was it. The query is unfortunately quite slow but works like a charm. – user3358102 Mar 14 '17 at 20:25
Make sure you have indexes on all the columns being used in the joins. – Barmar Mar 14 '17 at 20:34

How to update two tables in one mySQL query

1054 - Unknown column 'A.product_id' in 'on clause'

1 Answers1