ON Duplicate Key for exact combination of multiple values

Question

Let's assume we have this table:

--------------------------
| x  |  y  |  z  |  data  |
---------------------------
| 3  |  53 |  24 |  hello |
---------------------------

-

Now I only want to update "data" in case there is the exact combination of X, Y, Z.

INSERT INTO TABLE SET x=?,y=?,z=?,data=? ON DUPLICATE KEY UPDATE data=?

This obviously doesn't work. How would I do this?

Answer 1

You only add a composite unique key over the three fields x,y,z. the it works.

You can also use this syntax:

INSERT INTO TABLE (x,y,z,data) values (?,?,?,?) ON DUPLICATE KEY UPDATE data=?;