sql - MySQL update record if two columns match data, otherwise insert row -

i have simple mysql table 3 columns: "id" (int), "property" (varchar) , "value" (varchar).

all columns have able have identical records (i.e. several records same id).

the ideas save different properties matching different ids.

my question is, how go updating record if data matches both id , property, , otherwise add new record?

i thought using on duplicate key, won't work, none of columns unique.

here's example of desired behavior:

1: start table:

+----+----------+-------+ | id | property | value | +----+----------+-------+ | 45 | money    |   500 | +----+----------+-------+ 

2: insert: id = 45, property = sex, value = male, , get:

+----+----------+-------+ | id | property | value | +----+----------+-------+ | 45 | money    | 500   | | 45 | sex      | male  | +----+----------+-------+ 

3: insert: id = 45, property = money, value = 600, , get:

+----+----------+-------+ | id | property | value | +----+----------+-------+ | 45 | money    | 600   | | 45 | sex      | male  | +----+----------+-------+ 

4: lastly, can this: id = 46, property = money, value = 600, , get:

+----+----------+-------+ | id | property | value | +----+----------+-------+ | 45 | money    | 600   | | 45 | sex      | male  | | 46 | money    | 600   | +----+----------+-------+ 

your logic implies attempting insert new record id , property appear in table should instead cause record's value updated. mysql provides way this. first add unique constraint on id , property columns:

alter table yourtable add unique idx (id, property); 

then perform inserts using code:

insert yourtable (id, property, value) values (45, 'money', 600)     on duplicate key update value = 600; 

if wanted create table scratch unique constraint might try this:

create table yourtable (     id int not null,     property varchar(255) not null,     value int,     constraint uc_idp unique (id, property) );