SQL UPDATE 2 table with JOIN-CodePudding

I want to update two tables at the same time, because the connection and update conditions are in the first table. And only simple information like the amount is in the second table

UPDATE
order,
order_product
SET
order.total = order.total*0.00001,
order_product.price = order_product.price*0.00001,
order_product.total = order_product.total*0.00001
FROM
order_product
LEFT JOIN
order ON order_product.order_id = order.order_id
WHERE
order.currency_code = "USD"
AND
order.currency_value = 0.00001000

I keep getting this error

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FROM order_product LEFT JOIN order ON order_id ...' at line 5

CodePudding user response：

Your syntax is off for MySQL and looks to be taken from some other dialect. For a MySQL update join, use an explicit join as follows:

UPDATE order o
INNER JOIN order_product op
    ON op.order_id = o.order_id
SET
    o.total = o.total*0.00001,
    op.price = op.price*0.00001,
    op.total = op.total*0.00001
WHERE
    o.currency_code = 'USD' AND
    o.currency_value = 0.00001000;