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

mysql insert insert-update
Niko Gamulin picture Niko Gamulin · Dec 23, 2009 · Viewed 48.2k times · Source

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?

Answer

Andomar picture Andomar · Dec 23, 2009

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

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

Related questions

MySQL trigger On Insert/Update events

So I have two tables like this... ext_words ------------- | id | word | ------------- | 1 | this | ------------- | 2 | that | ------------- | 3 | this | ------------- ext_words_count --------------------- | id | word | count | --------------------- | 1 | this | 2 | --------------------- | 2 | that | 1 | --------------------- I am trying to create a trigger that will: …

Read More
Why are 2 rows affected in my `INSERT ... ON DUPLICATE KEY UPDATE`?

I'm doing an INSERT ... ON DUPLICATE KEY UPDATE for a PRIMARY KEY in the following table: DESCRIBE users_interests; +------------+---------------------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+---------------------------------+------+-----+---------+-------+ | uid | int(11) | NO | …

Read More
How to improve INSERT performance on a very large MySQL table

I am working on a large MySQL database and I need to improve INSERT performance on a specific table. This one contains about 200 Millions rows and its structure is as follows: (a little premise: I am not a database expert, …

Read More