Search code examples
mysqlkeyon-duplicate-key

ON Duplicate Key for exact combination of multiple values

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?

Solution

  • 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=?;