How to INSERT a record or UPDATE if it already exi

问题:

I have a table with columns record_id (auto inc), sender, sent_time and status.

In case there isn't any record of a particular sender, for example "sender1", I have to INSERT a new record otherwise I have to UPDATE the existing record which belongs to "user1".

So if there isn't any record already stored, I would execute

# record_id is AUTO_INCREMENT field
INSERT INTO messages (sender, sent_time, status)
VALUES (@sender, time, @status)

Otherwise I would execute UPDATE statement.

Anyway.. does anyone know how to combine these two statements in order to insert a new record if there isn't any record where the field sender value is "user1" otherwise update the existing record?

回答1:

MySQL supports the insert-on-duplicate syntax, f.e.:

INSERT INTO table (key,col1) VALUES (1,2)
  ON DUPLICATE KEY UPDATE col1 = 2;

回答2:

If you have solid constraints on the table, then you can also use the REPLACE INTO for that. Here's a cite from MySQL:

REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.

The syntax is basically the same as INSERT INTO, just replace INSERT by REPLACE.

INSERT INTO messages (sender, sent_time, status) VALUES (@sender, time, @status)

would then be

REPLACE INTO messages (sender, sent_time, status) VALUES (@sender, time, @status)

Note that this is a MySQL-specific command which doesn't occur in other DB's, so keep portability in mind.

回答3:

As others have mentioned, you should use "insert...on duplicate key update", sometimes referred to as an "upsert". However, in your specific case you don't want to use a static value in the update, but rather the values you pass in to the values clause of the insert statement.

Specifically, I think you want to update two columns if the row already exists:

1) sent_time
2) status

In order to do this, you would use an "upsert" statement like this (using your example):

INSERT INTO messages (sender, sent_time, status) 
VALUES (@sender, time, @status)
ON DUPLICATE KEY UPDATE 
  sent_time = values(sent_time),
  status = values(status);

回答4:

Check out "Insert on Duplicate Key Update".

INSERT INTO table (a,b,c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE c=c+1;

UPDATE table SET c=c+1 WHERE a=1;

回答5:

One options is using on duplicate update syntax

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

Other options is doing select to figure out if record exists and then doind inser/update accordingly. Mind that if you're withing transaction select will not explicitly terminate the transaction so it's safe using it.

回答6:

use merge statement :

merge into T1
          using T2
          on (T1.ID = T2.ID)
    when  matched
    then  update set  
                      T1.Name = T2.Name
    when  not matched
    then  insert values (T2.ID,T2.Name);