mysql替换成替代

问题描述：

我当前正在使用replace into语句，我有一个唯一字段，如果发现重复项，它将导致UPDATE而不是INSERT ...

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

答

您将要使用INSERT ... ON DUPLICATE KEY UPDATE语法.

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

下面是一个示例，该示例将尝试创建具有ID，生日和名称的记录.如果存在带有id字段的记录，它将执行指定的更新.表格中还有许多其他字段，例如电子邮件地址，邮政编码等.如果要更新，我想不理这些字段. (如果我没有在REPLACE INTO语句中包括这些数据，REPLACE INTO将会丢失所有这些数据.)

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';

mysql替换成替代

相关推荐