Stack Overflow
Stack Overflow

Questions tagged [insert-update]

A mixed flavour of data entry mode and data modification mode. It can be used when insert is required for case of non-existing data and modification if data exists.

1300 questions
1142
votes
12 answers

Insert into a MySQL table or update if exists

I want to add a row to a database table, but if a row exists with the same unique key I want to update the row. For example: INSERT INTO table_name (ID, NAME, AGE) VALUES(1, "A", 19); Let’s say the unique key is ID, and in my Database, there is a…
Keshan
  • 14,251
  • 10
  • 48
  • 72
390
votes
7 answers

How to UPSERT (MERGE, INSERT ... ON DUPLICATE UPDATE) in PostgreSQL?

A very frequently asked question here is how to do an upsert, which is what MySQL calls INSERT ... ON DUPLICATE UPDATE and the standard supports as part of the MERGE operation. Given that PostgreSQL doesn't support it directly (before pg 9.5), how…
Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
163
votes
8 answers

SQL Server insert if not exists best practice

I have a Competitions results table which holds team member's names and their ranking on one hand. On the other hand I need to maintain a table of unique competitors names: CREATE TABLE Competitors (cName nvarchar(64) primary key) Now I have some…
Didier Levy
  • 3,393
  • 9
  • 35
  • 57
158
votes
2 answers

How do I update if exists, insert if not (AKA "upsert" or "merge") in MySQL?

Is there an easy way to INSERT a row when it does not exist, or to UPDATE if it exists, using one MySQL query?
blub
  • 2,146
  • 4
  • 20
  • 19
148
votes
4 answers

INSERT INTO ... SELECT FROM ... ON DUPLICATE KEY UPDATE

I'm doing an insert query where most of many columns would need to be updated to the new values if a unique key already existed. It goes something like this: INSERT INTO lee(exp_id, created_by, location, animal, …
dnagirl
  • 20,196
  • 13
  • 80
  • 123
112
votes
2 answers

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 |…
Josh Smith
  • 14,674
  • 18
  • 72
  • 118
84
votes
5 answers

Update a column in MySQL

I have a table table1 with three columns and a bunch of rows: [key_col|col_a|col_b] I want to update col_a with a set of values (i.e. leaving col_b unchanged), something like this: INSERT INTO table1 AS t1 (key_col, col_a) VALUES ("k1", "foo"),…
Muleskinner
  • 14,150
  • 19
  • 58
  • 79
62
votes
11 answers

.NET Dictionary: get existing value or create and add new value

I often find myself creating a Dictionary with a non-trivial value class (e.g. List), and then always writing the same code pattern when filling in data. For example: var dict = new Dictionary>(); string key = "foo"; string…
Rok Strniša
  • 6,781
  • 6
  • 41
  • 53
48
votes
7 answers

Is there a way to do an "INSERT...ON DUPLICATE KEY UPDATE" in Zend Framework 1.5?

I would like to use ON DUPLICATE KEY UPDATE in Zend Framework 1.5, is this possible? Example INSERT INTO sometable (...) VALUES (...) ON DUPLICATE KEY UPDATE ...
danielrsmith
  • 4,050
  • 3
  • 26
  • 32
44
votes
5 answers

Does DB2 have an "insert or update" statement?

From my code (Java) I want to ensure that a row exists in the database (DB2) after my code is executed. My code now does a select and if no result is returned it does an insert. I really don't like this code since it exposes me to concurrency…
Mikael Eriksson
  • 493
  • 1
  • 4
  • 7
44
votes
5 answers

T-SQL Insert or update

I have a question regarding performance of SQL Server. Suppose I have a table persons with the following columns: id, name, surname. Now, I want to insert a new row in this table. The rule is the following: If id is not present in the table, then…
Markus
  • 3,547
  • 10
  • 39
  • 55
43
votes
3 answers

How to update elements within a heap? (priority queue)

When using a min/max-heap algorithm, priorities may change. One way to handle this is to removal and insert the element to update the queue order. For priority queues implemented using arrays, this can be a performance bottleneck that seems…
ideasman42
  • 42,413
  • 44
  • 197
  • 320
35
votes
6 answers

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…
Niko Gamulin
  • 66,025
  • 95
  • 221
  • 286
30
votes
3 answers

Django - save() update on duplicate key

I have little application which allows a user to rate a video. The user can rate only once. So I have defined the uniqueness on the model. But he should be able change his rate. So the save() should update on duplicate key class…
Pierre de LESPINAY
  • 44,700
  • 57
  • 210
  • 307
27
votes
4 answers

How do you determine if an insert or update was successful using Java and MySQL?

I am using Java to connect to a MySQL database. I am trying to insert or update data into the database. Even though I am quite sure the insert was successful, it returns false. According to the "execute" API, the return value is "true if the…
user3239558
  • 1,757
  • 4
  • 18
  • 31
1
2 3
…
86 87