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 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.