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.
