MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields

mysql, sql-insert, sql-update, upsert

Solution

Consider

INSERT INTO table (a,b,c) VALUES (1,2,3)
    -> ON DUPLICATE KEY UPDATE c=c+1;

If a and b are `UNIQUE` fields, `UPDATE` occurs on `a = 1 OR b = 2`. Also when condition `a = 1 OR b = 2` is met by two or more entries, update is done only once.

Ex here table table with Id and Name `UNIQUE` fields

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6

If query is

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7);

then we get

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6
1      C        7

which violates uniqueness of Id and Name. Now with

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7)
ON DUPLICATE KEY UPDATE Value = 7;

we get

Id     Name     Value 
1      P        7 
2      C        7 
3      D        29 
4      A        6

Behavior on multiple keys is the following

`UPDATE` in `ON DUPLICATE KEY UPDATE` is performed if one of the `UNIQUE` field equals the value to be inserted. Here, `UPDATE` is performed on `Id = 1 OR Name = C`. It is equivalent to

UPDATE table 
SET Value = 7
WHERE Id = 1 OR Name = C;

What if I want one update only, for either key

Can use `UPDATE` statement with `LIMIT` keyword

UPDATE table 
SET Value = 7
WHERE Id = 1 OR Name = C
LIMIT 1;

which will give

Id     Name     Value 
1      P        7 
2      C        3 
3      D        29 
4      A        6

What if I want one update only if values for both keys are matched

One solution is to `ALTER TABLE` and make the `PRIMARY KEY` (or uniqueness) work on both fields.

ALTER TABLE table 
DROP PRIMARY KEY,
ADD PRIMARY KEY (Id, Name);

Now, on

INSERT INTO table (Id, Name, Value)
VALUES (1, C, 7)
ON DUPLICATE KEY UPDATE Value = 7;

we get

Id     Name     Value 
1      P        2 
2      C        3 
3      D        29 
4      A        6
1      C        7

since no duplicate (on both keys) is found.

Problem

From MySQL 4.1.0 onwards, it is possible to add `ON DUPLICATE KEY UPDATE` statement to specify behavior when values inserted (with `INSERT` or `SET` or `VALUES`) are already in destination table w.r.t. `PRIMARY KEY` or some `UNIQUE` field. If value for `PRIMARY KEY` or some `UNIQUE` field are already in table, `INSERT` is replaced by an `UPDATE`. How does `ON DUPLICATE KEY UPDATE` behave in case there are multiple `UNIQUE` fields in my table ? Can I have one update only, if either `UNIQUE` field is matched ? Can I have an update only if both `UNIQUE` fields are matched simultaneously ?

Original source