Convert UPDATE with INNER JOIN from SQL for use in MySQL

Question

I'd like to convert this for us in MySQL:

UPDATE product 
SET price = 12.95 
FROM product 
    INNER JOIN product_to_category ON product.product_id = product_to_category.product_id 
    INNER JOIN category ON product_to_category.category_id = category.category_id 
        AND category.parent_id = 39

MySQL doesn't like the FROM portion, and I'm not sure that the INNER JOINs will work as written either.

Answer 1

UPDATE product 
SET price = 12.95 
WHERE product_id in
(SELECT product_id 
FROM product_to_category 
INNER JOIN category 
ON product_to_category.category_id = category.category_id 
WHERE category.parent_id = 39)

Answer 2

I don't have your database, so I can't really test, but I suppose you could use the multi-table syntax for your update statement :

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET col_name1={expr1|DEFAULT} [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

Quoting the manual :

For the multiple-table syntax, UPDATE updates rows in each table named in table_references that satisfy the conditions.

In your case, something like this might do the trick :

UPDATE product, product_to_category, category
SET product.price = 12.95 
WHERE product.product_id = product_to_category.product_id 
    AND product_to_category.category_id = category.category_id 
    AND category.parent_id = 39

Hope this helps !

Answer 3

The general syntax for a multiple-table UPDATE statement is

UPDATE <table>
    (INNER|LEFT|RIGHT|...) JOIN <table2> ON <condition> 
SET <field>=<value>
WHERE <condition>

So your statement should work if you rewrite it to

UPDATE product 
    INNER JOIN product_to_category ON product.product_id = product_to_category.product_id
    INNER JOIN category ON product_to_category.category_id = category.category_id AND category.parent_id = 39
SET price = 12.95

(untested, I don't have a mysql instance at hand)

Answer 4

UPDATE product 
    INNER JOIN product_to_category ON product.product_id = product_to_category.product_id 
    INNER JOIN category ON product_to_category.category_id = category.category_id 
        AND category.parent_id = 39
SET product.price = 12.95