how to insert a record or update if it already exists?

Question

Hi,

I have a table with columns

record_id, sender, sent_time, status and 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

INSERT INTO messages (sender, sent_time, status) VALUES (@sender, time, @status) (record_id is auto_ncrementing field).

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?

Thank you!

Answer 1

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;

Answer 2

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

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

Answer 3

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.

Answer 4

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

Answer 5

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.