mysql - Can I use INSERT INTO ... On DUPLICATE KEY without by using auto_increment value? -

i trying write query check if record exists (based on couple of clause , not unique identifier) if such search return records need update found records if nothing found need insert record. note can't use if exists because trying make query client side script , not server side. came cross idea of insert .... on duplicate key

can without knowing row key identifier? if find record accountid = 17 , name = 'mike' update make name 'mike a' if there no record these 2 clause insert record.

this attempt giving me syntax error

insert test (name, accountid) values ('mike', 17)   on duplicate key  update test set name='mike a' name ='mike' , accountid = 17 

can method handle trying do? if yes can please correct syntax?

thank you

the way can work if have primary key or unique constraint on fields. documentation:

if specify on duplicate key update, , row inserted cause duplicate value in unique index or primary key, update of old row performed. example, if column declared unique , contains value 1, following 2 statements have identical effect:

create table test (name varchar(100), accountid int); insert test values ('mike', 17); alter table test add unique (name, accountid);  insert int test (name, accountid) values ('mike', 17) on duplicate key update name='mike a'; 

• sql fiddle demo

without unique key, insert duplicate record.