Stack Overflow
Stack Overflow

Questions tagged [upsert]

UPSERT is a combination of UPDATE and INSERT, typically used in relational databases.

UPSERT refers to types of statements (usually database statements) that INSERT an item (record) to a resource (table in a database) where, if the item already exists, it will update the existing item with the fields provided. The term UPSERT is an amalgamation of UPDATE and INSERT and is common slang among database developers.

1227 questions
112
votes
9 answers

Insert Update stored proc on SQL Server

I've written a stored proc that will do an update if a record exists, otherwise it will do an insert. It looks something like this: update myTable set Col1=@col1, Col2=@col2 where ID=@ID if @@rowcount = 0 insert into myTable (Col1, Col2) values…
Guy
  • 65,082
  • 97
  • 254
  • 325
101
votes
8 answers

Updating the path 'x' would create a conflict at 'x'

This error happens when I tried to update upsert item: Updating the path 'x' would create a conflict at 'x'
zored
  • 2,718
  • 3
  • 19
  • 23
93
votes
6 answers

How to Perform an UPSERT so that I can use both new and old values in update part

Stupid but simple example: Assume I have a table 'Item' where I keeps totals of the items that receive. Item_Name Items_In_Stock Item name is primary key here. How to i achieve the following when ever I receive item A in quantity X.…
WPFAbsoluteNewBie
  • 1,285
  • 2
  • 10
  • 21
90
votes
10 answers

How do I UPDATE a row in a table or INSERT it if it doesn't exist?

I have the following table of counters: CREATE TABLE cache ( key text PRIMARY KEY, generation int ); I would like to increment one of the counters, or set it to zero if the corresponding row doesn't exist yet. Is there a way to do this…
Remy Blank
  • 4,236
  • 2
  • 23
  • 24
75
votes
4 answers

SQLite "INSERT OR REPLACE INTO" vs. "UPDATE ... WHERE"

I've never seen the syntax INSERT OR REPLACE INTO names (id, name) VALUES (1, "John") used in SQL before, and I was wondering why it's better than UPDATE names SET name = "John" WHERE id = 1. Is there any good reason to use one over the other. Is…
nevan king
  • 112,709
  • 45
  • 203
  • 241
68
votes
2 answers

syntax for single row MERGE / upsert in SQL Server

I'm trying to do a single row insert/update on a table but all the examples out there are for sets. Can anyone fix my syntax please: MERGE member_topic ON mt_member = 0 AND mt_topic = 110 WHEN MATCHED THEN UPDATE SET mt_notes = 'test' WHEN NOT…
Jacob
  • 7,741
  • 4
  • 30
  • 24
60
votes
2 answers

Bulk insert, update if on conflict (bulk upsert) on Postgres

I am writing a data-mining program, which bulk inserts user data. The current SQL is just a plain bulk insert: insert into USERS( id, username, profile_picture) select unnest(array['12345']), unnest(array['Peter']), …
MK Yung
  • 4,344
  • 6
  • 30
  • 35
56
votes
6 answers

Fast or Bulk Upsert in pymongo

How can I do a bulk upsert in pymongo? I want to Update a bunch of entries and doing them one at a time is very slow. The answer to an almost identical question is here: Bulk update/upsert in MongoDB? The accepted answer doesn't actually answer the…
ComputationalSocialScience
  • 1,947
  • 3
  • 17
  • 18
54
votes
1 answer

Differentiate inserted and updated rows in UPSERT using system columns

Several questions have been asked recently here on SO about how to differentiate inserted and updated rows in a PostgreSQL UPSERT statement (INSERT ... ON CONFLICT ... DO UPDATE ...). Here is a simple example: create table t(i int primary key, x…
Abelisto
  • 14,826
  • 2
  • 33
  • 41
50
votes
4 answers

How can I use use Entity Framework to do a MERGE when I don't know if the record exists?

In this SO answer about Entity Framework and MERGE, the example for how to code it is this: public void SaveOrUpdate(MyEntity entity) { if (entity.Id == 0) { context.MyEntities.AddObject(entity); } else { …
Joshua Frank
  • 13,120
  • 11
  • 46
  • 95
49
votes
5 answers

MongoDB: upsert sub-document

I have documents that looks something like that, with a unique index on bars.name: { name: 'foo', bars: [ { name: 'qux', somefield: 1 } ] } . I want to either update the sub-document where { name: 'foo', 'bars.name': 'qux' } and $set: {…
shesek
  • 4,584
  • 1
  • 28
  • 27
48
votes
4 answers

serial in postgres is being increased even though I added on conflict do nothing

I'm using Postgres 9.5 and seeing some wired things here. I've a cron job running ever 5 mins firing a sql statement that is adding a list of records if not existing. INSERT INTO sometable (customer, balance) VALUES (:customer, :balance) ON…
Christian
  • 6,961
  • 10
  • 54
  • 82
48
votes
5 answers

Atomic UPSERT in SQL Server 2005

What is the correct pattern for doing an atomic "UPSERT" (UPDATE where exists, INSERT otherwise) in SQL Server 2005? I see a lot of code on SO (e.g. see Check if a row exists, otherwise insert) with the following two-part pattern: UPDATE ... FROM…
rabidpebble
  • 567
  • 1
  • 5
  • 8
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
43
votes
3 answers

How to correctly do upsert in postgres 9.5

correct syntax of upsert with postgresql 9.5, below query shows column reference "gallery_id" is ambiguous error , why? var dbQuery = `INSERT INTO category_gallery ( category_id, gallery_id, create_date, create_by_user_id ) VALUES ($1, $2, $3,…
user1575921
  • 1,078
  • 1
  • 16
  • 29
1
2
3
…
81 82