On Duplicate Key Update won't update, but throws an error for duplicates

Question

I have a table with a two-column primary key (key1,key2).

This is my query

INSERT INTO mytable (key1, key2, val1, val2) 
VALUES (:k1, :k2, :v1, :v2)
ON DUPLICATE KEY UPDATE val1 = val1 + :v1, val2 = val2 + :v2

Now when I insert a row with a duplicate key, I get this error message:

Uncaught exception 'PDOException' with message 'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '157-433' for key "PRIMARY" in ...

Why doesn't it just update the existing (157,433)-row?

Answer 1

You can't refer to the same placeholder multiple times in a query. MySQL has a built-in solution to this -- if you use VALUES(colname) in the ON DUPLICATE KEY clause, it refers to the value that would have been stored in the column if it hadn't been a duplicate. So use:

INSERT INTO mytable (key1, key2, val1, val2)
VALUES (:k1, :k2, :v1, :v2)
ON DUPLICATE KEY UPDATE val1 = val1 + VALUES(val1), val2 = val2 + VALUES(val2)

I'm not sure why it's causing an error about creating a duplicate entry, though.