mysql - Insert or update based on row data -

first off, know replace into , insert ... on key duplicate update not i'm looking -or- don't know how use them achieve want.

this simple table structure:

+-----------+---------+----------+----------+ | player_id | item_id | quantity | location | +-----------+---------+----------+----------+ 

my insert query looks this:

insert items values (2, 10, 40, 1); 

now, if there row fields match, except quantity (doesn't matter if matches or not, point other 3 match). so, if there's row player_id 2, item_id 10 , location 1 (quantity value doesn't matter - can 40, doesn't have to), want update it, rather insert new one.

obviously, i'm looking way different select + update, if there any...

if there no other constraints considered, couldn't add combined unique key on (player_id, item_id , location), , go insert ... on duplicate key update?

edit: trying clarify. suppose have following table creation statement:

create table items (     player_id int not null,     item_id int not null,     quantity int not null,     location int not null ) engine = innodb character set utf8 collate utf8_unicode_ci; 

you add combined unique index 3 columns:

alter table items add unique player_item_location (player_id, item_id, location); 

so can insert row:

insert items (player_id, item_id, quantity, location) values (2, 10, 40, 1); 

and if try execute same insert again, end message:

#1062 - duplicate entry '2-10-1' key 'player_item_location' 

but if add on duplicate key update this:

insert items (player_id, item_id, quantity, location) values (2, 10, 30, 1) on duplicate key update quantity = 30; 

you end in not adding row, updating existing 1 (player 2, item 10, location 1) , changing quantity 40 30.

and, if want add row, player 3, item 10, location 1, work, too:

insert items (player_id, item_id, quantity, location) values (3, 10, 40, 1); 

so after 3 inserts, should end in having following rows in table:

mysql> select * items; +-----------+---------+----------+----------+ | player_id | item_id | quantity | location | +-----------+---------+----------+----------+ |         2 |      10 |       30 |        1 | |         3 |      10 |       40 |        1 | +-----------+---------+----------+----------+ 2 rows in set (0.00 sec) 

based on question, thought behaviour wanted have. if not, please let know doesn't work or didn't understand correctly.