MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields

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        61      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 = 7WHERE 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 = 7WHERE Id = 1 OR Name = CLIMIT 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        61      C        7

since no duplicate (on both keys) is found.

1. how does MySQL behave ...It behaves as expected, that is executes ON DUPLICATE KEY clause.

2. Can I have one update for either...In reality, you have only one ON DUPLICATE KEY clause, so you need to put some code to differentiate which constraint was involved. Fortunatelly, it is possible. The only thing you should know, the order of assignment matters, and you can assign multiple times. Suppose, you have unique constraint on a and b, and you want to update c only if a uniqueness is involved: ... KEY UPDATE c = IF(a = VALUES(a) and b <> VALUES(b), VALUES(c), c), b = VALUES(b)

   but if you change the order of assignments the second condition within if will be always false.

3. See 2.