MySQL behavior of ON DUPLICATE KEY UPDATE for multiple UNIQUE fields -

from mysql 4.1.0 onwards, possible add on duplicate key update statement specify behavior when values inserted (with insert or set or values) in destination table w.r.t. primary key or unique field. if value primary key or unique field in table, insert replaced update.

• how on duplicate key update behave in case there multiple unique fields in table ?

• can have 1 update only, if either unique field matched ?

• can have update if both unique fields matched simultaneously ?

consider

insert table (a,b,c) values (1,2,3)     -> on duplicate key update c=c+1; 

if , b unique fields, update occurs on a = 1 or b = 2. when condition a = 1 or b = 2 met 2 or more entries, update done once.

ex here table table id , name unique fields

id     name     value  1      p        2  2      c        3  3      d        29  4             6 

if query is

insert table (id, name, value) values (1, c, 7) 

then get

id     name     value  1      p        2  2      c        3  3      d        29  4             6 1      c        7 

which violates uniqueness of id , name. with

insert 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             6 

behavior on multiple keys following

update in on duplicate key update performed if 1 of unique field equals value inserted. here, update performed on id = 1 or name = c. equivalent

update table  set value = 7 id = 1 or name = c 

what if want 1 update only, either key

can use update statement limit keyword

update table  set value = 7 id = 1 or name = c limit 1; 

which give

id     name     value  1      p        7  2      c        3  3      d        29  4             6 

what if want 1 update if values both keys matched

one solution alter table , make primary key (or uniqueness) work on both fields.

alter table table  drop primary key add primary key (id, name); 

now, on

insert table (id, name, value) values (1, c, 7) on duplicate key update value = 7 

we

id     name     value  1      p        2  2      c        3  3      d        29  4             6 1      c        7 

since no duplicate (on both keys) found.