INSERT ... ON DUPLICATE KEY UPDATE

Question

I am using a API to fetch records from a different server and insert into my local database but when a particular field say apiserverID is duplicate i just want to update fields. my problem is that i have table structure as

• myPrimaryKey
• apiserverID
• ....
• .....
• .....
• updateDate

now i want simple procedure to update the row if apiserverID is duplicate. Only solution i know is i have to check (SELECT) if the key apiserverID exist then update else insert. but i don't want do programming for this is this possible in one query

EDIT : Main problem is that INSERT ... ON DUPLICATE KEY UPDATE don't work for a particular field, it include all the keys to check duplicity

Answer 1

if there is a single multiple-column unique index on the table, then the update uses (seems to use) all columns (of the unique index) in the update query.

So if there is a UNIQUE(a,b) constraint on the table in the example, then the INSERT is equivalent to this UPDATE statement:

UPDATE table SET c=c+1 WHERE a=1 AND b=2;

(and not "a=1 OR b=2")

But in your case it will work as expected because first one is your primary key and it will never be duplicate form the server so only thing which can be duplicate is your apiserverID so when ever found duplicate it will update the row else always a new insert will be executed