SQL How to Update by INNER JOIN -

Question

Please help me figure this out since I tried everything from this forum but still haven't found a solution.

Well, I have two tables:

• prices
• manufacturers

I want to change the values of two fields that are both in table prices. And I will just give specific values to those.

The fields are:

• prices.override (in which I want to give the value 0) and
• prices.product_discount_id (in which I want to give the value 66)

BUT I want to change the fields ONLY FOR the manufacturer with ID 31.

So, I first check that an INNER JOIN works fine.

SELECT manufacturers.manufacturer_id,
prices.product_id,
prices.product_price,
prices.override,
prices.product_discount_id
FROM manufacturers
INNER prices
ON manufacturers.product_id=prices.product_id
AND manufacturers.manufacturer_id=31;

---

But when I try to update the two fields, I do not know how to make that work. For example, I tried this but it didn't work:

UPDATE prices
SET prices.override=1
FROM
INNER JOIN prices
ON manufacturers.product_id=prices.product_id
AND manufacturers.manufacturer_id=31;

I also tried this:

UPDATE prices
SET prices.override=1, 
INNER JOIN manufacturers 
ON prices.virtuemart_product_id = manufacturers.virtuemart_product_id 
AND manufacturers.manufacturer_id=31;

What did i do wrong? Usually the error message I get is:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM jos_virtuemart_product_prices prices INNER JOIN jos_virtuemart_product_man' at line 3

I read something for alias but still no result.

Any help would be appreciated!

Answer 1

You have a few syntax problems , MySQL UPDATE..JOIN syntax is :

UPDATE T
JOIN t2 ON()
SET ..
WHERE ..

Secondly, you had an unnecessary comma after the SET prices.override=1, so:

UPDATE prices
INNER JOIN manufacturers 
ON prices.virtuemart_product_id = manufacturers.virtuemart_product_id 
    AND manufacturers.manufacturer_id=31
SET prices.override=1