mysql replace into alternative

问题:

i'm currently using a replace into statement, I have a unique field which will cause it to UPDATE rather than INSERT if it finds a duplicate...

Problem is if it finds a duplicate i can't get to update on a few columns, it just wipes the lot.

Is there a similar "one statement" method where I can just UPDATE what I want?

I've found merge into but don't undertsnad the first bit about merge into table using table

回答1:

You're going to want to use the INSERT...ON DUPLICATE KEY UPDATE syntax.

http://dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

Here's an example that will try to create a record with an id, birthday, and name. If a record with the id field exists, it will do the update specified. The table has lots of other fields like email address, zip code, etc. I want to leave those fields alone if I update. (REPLACE INTO would lose any of that data if I didn't include it in the REPLACE INTO statement.)

INSERT INTO user (userid,birthday,first_name,last_name) 
   VALUES (1234,'1980-03-07','Joe','Smith') 
ON DUPLICATE KEY UPDATE 
   birthday = '1980-03-07',
   first_name = 'Joe', 
   last_name = 'Smith';