SQL UPDATE 2 table with JOIN

November 30, 2022

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
"

>Solution :

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;