Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 251
Niko Gamulin Avatar asked Dec 23 '09 13:12

Niko Gamulin


People also ask

How do I add a new record to an existing table?

To insert records into a table, enter the key words insert into followed by the table name, followed by an open parenthesis, followed by a list of column names separated by commas, followed by a closing parenthesis, followed by the keyword values, followed by the list of values enclosed in parenthesis.

How do you insert a new record in a table if not exists?

There are three ways you can perform an “insert if not exists” query in MySQL: Using the INSERT IGNORE statement. Using the ON DUPLICATE KEY UPDATE clause. Or using the REPLACE statement.


1 Answers

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

INSERT INTO table (key,col1) VALUES (1,2)   ON DUPLICATE KEY UPDATE col1 = 2; 
like image 82
Andomar Avatar answered Sep 22 '22 23:09

Andomar