HeidiSQl error for MariaDB for wrong syntax

Question

I am 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

Here is my SQL:

UPDATE product
SET cost_price = db2.supplier.Cost_price
FROM product, db2.supplier WHERE product.SKU = db2.supplier.SKU;

How can I resolve this?

Answer 1

Try using this join syntax:

UPDATE product
INNER JOIN  db2.supplier ON product.SKU = db2.supplier.SKU
SET product.cost_price = db2.supplier.Cost_price

Answer 2

There is no FROM clause for the UPDATE in MariaDB. Just use JOIN instead:

UPDATE product p JOIN
       db2.supplier s
       ON p.SKU = s.SKU
    SET p.cost_price = s.Cost_price;

Note the use of table aliases and explicit JOIN syntax!