Insert with on duplicate key update gives error 1064

Question

I'm trying to use this query but whatever I do I cannot get it to work. I'm still very new to the on duplicate key update syntax, but I can't find anything wrong with it

INSERT INTO product_leverancier (product_id, leverancier_id, prijs) 
SELECT i.product_id, i.leverancier_id, i.prijs FROM import_tbl i 
ON DUPLICATE KEY UPDATE product_id=VALUES(product_id), 
leverancier_id=VALUES(leverancier_id), prijs=VALUES(prijs)

The error I get is this:

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 'UPDATE product_id=VALUES(product_id), leverancier_id=VALUES(leverancier_id), pr' at line 2 Error code 1064.

And whatever I change it's always the same error and error code. Any idea what the problem is?

Answer 1

Your syntax is a bit off, and I don't believe that VALUES is used when using a SELECT as the source of the insert. Instead, use that source table for the update values:

INSERT INTO product_leverancier (product_id, leverancier_id, prijs) 
SELECT i.product_id, i.leverancier_id, i.prijs
FROM import_tbl i 
ON DUPLICATE KEY UPDATE
    product_id     = i.product_id, 
    leverancier_id = i.leverancier_id,
    prijs          = i.prijs

Note that the alias i is required when referring to the columns in the source table.

Here is a good reference question which delves deeper into the syntax of ON DUPLICATE KEY UPDATE when it is used with INSERT INTO ... SELECT:

INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE