How to INSERT a record or UPDATE if it already exists?

Question

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?

Solution 1

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

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

Solution 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.

Solution 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);

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

Solution 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.

View more solutions