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?

score 50 · Accepted Answer · answered Dec 23 '09 at 13:52

50

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

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

answered Dec 23 '09 at 13:52

Andomar

232,371
49
380
404

2

The syntax is even more powerful than that: "ON DUPLICATE KEY UPDATE col1 = VALUES(col1) is a more elegant way to do what you describe (and it works on multi-row inserts). You can also use the value of col1, as in ON DUPLICATE KEY UPDATE col1 = col1 + 1. – ojrac Dec 23 '09 at 14:47
why do you do col1=col1+1. What does that mean? – user4951 Aug 09 '11 at 05:00
1

Stumbled across this thread... col1 = col1 +1 is an example where perhaps you have a count in your table, rather than using SELECT to get the value and add 1, it will reference the current value in the table, and perform a calculation making the SELECT statement redundant. – tutts Nov 08 '11 at 13:15

score 13 · Answer 2 · answered Dec 23 '09 at 14:52

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.

Well deleting the old row will cause complications when the row is a parent of many other tables. Those with cascade delete will delete all the relationship. We really just want to do update. — user4951, Aug 09 '11 at 05:03

score 5 · Answer 3 · answered Dec 23 '09 at 14:42

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

score 3 · Answer 4 · answered Dec 23 '09 at 13:51

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 Dec 23 '09 at 13:51

Sampson

265,109
74
539
565

1

Not true - "ON DUPLICATE KEY UPDATE (added in MySQL 4.1.0)" – Piskvor left the building Dec 23 '09 at 13:54

score 1 · Answer 5 · answered Dec 23 '09 at 13:53

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.

score -1 · Answer 6 · answered Jul 05 '13 at 12:48

-1

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

answered Jul 05 '13 at 12:48

Rajeev Kumar Mehta

69
1
3

1

MySQL does not support merge statement. – Omar Dulaimi Sep 28 '21 at 09:20

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

6 Answers6

Linked