Alex Rivera | Logout

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

Asked 2009-12-23T13:50:11.640
35

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?

Edit
Report

1 Answer

3

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;
answered 2009-12-23T13:51:28.857

Your Answer