SQL UPDATE with INNER JOIN

Question

I'm trying to use this query

UPDATE products  
SET products.product_price = '87.00000'
FROM products
INNER JOIN product_category
ON products.product_id = product_category.product_id 
WHERE product_category.category_id = '64'

However I receive this error: #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 table products INNER JOIN table product_category ON prod' at line 3

I don't see any syntax error. I have made this query from examples on this forum.

Answer 1

remove the FROM products line. and put the SET ... line just before the WHERE clause.

to be clear :

UPDATE ...
JOIN ...
SET ...
WHERE ...

you could also do

UPDATE products p
SET p.product_price='87.00000'
WHERE EXISTS (SELECT NULL 
              FROM product_category pc
              WHERE p.product_id = pc.product_id
              AND pc.category_id = '64');

Answer 2

You can use the following:

UPDATE products 
INNER JOIN product_category
  ON products.product_id = product_category.product_id 
SET products.product_price = '87.00000'
WHERE product_category.category_id = '64';

See SQL Fiddle with Demo

Answer 3

I think the SET clause needs to be after the table references and before the WHERE clause, like this:

UPDATE products
 INNER
  JOIN product_category
    ON products.product_id = product_category.product_id 
   SET products.product_price = '87.00000'
 WHERE product_category.category_id = '64'

---

Here's how I get that syntax when I need an UPDATE like that. I always start with a select statement, and that lets me know which rows are going to be updated. For example:

SELECT p.*, c.*
  FROM products p
  JOIN product_category c
    ON p.product_id = c.product_id 
 WHERE c.category_id = '64'

To convert that to an update statement, I add a "SET" clause after the table references and before the WHERE clause, and then replace "SELECT ... FROM" with the "UPDATE" keyword. Voila.

Answer 4

UPDATE products
INNER JOIN product_category
ON products.product_id = product_category.product_id 
SET products.product_price = '87.00000'
WHERE product_category.category_id = '64'