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
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
786
votes
18 answers

Insert, on duplicate update in PostgreSQL?

Several months ago I learned from an answer on Stack Overflow how to perform multiple updates at once in MySQL using the following syntax: INSERT INTO table (id, field, field2) VALUES (1, A, X), (2, B, Y), (3, C, Z) ON DUPLICATE KEY UPDATE…
Teifion
  • 108,121
  • 75
  • 161
  • 195
728
votes
22 answers

Solutions for INSERT OR UPDATE on SQL Server

Assume a table structure of MyTable(KEY, datafield1, datafield2...). Often I want to either update an existing record, or insert a new record if it doesn't exist. Essentially: IF (key exists) run update command ELSE run insert command What's…
Chris Cudmore
  • 29,793
  • 12
  • 57
  • 94
653
votes
22 answers

Postgres: INSERT if does not exist already

I'm using Python to write to a postgres database: sql_string = "INSERT INTO hundred (name,name_slug,status) VALUES (" sql_string += hundred + ", '" + hundred_slug + "', " + status + ");" cursor.execute(sql_string) But because some of my rows are…
AP257
  • 89,519
  • 86
  • 202
  • 261
620
votes
19 answers

UPSERT *not* INSERT or REPLACE

http://en.wikipedia.org/wiki/Upsert Insert Update stored proc on SQL Server Is there some clever way to do this in SQLite that I have not thought of? Basically I want to update three out of four columns if the record exists. If it does not exist, I…
Mike Trader
  • 8,564
  • 13
  • 55
  • 66
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
350
votes
9 answers

INSERT IF NOT EXISTS ELSE UPDATE?

I've found a few "would be" solutions for the classic "How do I insert a new record or update one if it already exists" but I cannot get any of them to work in SQLite. I have a table defined as follows: CREATE TABLE Book ID INTEGER PRIMARY KEY…
SparkyNZ
  • 6,266
  • 7
  • 39
  • 80
328
votes
11 answers

Oracle: how to UPSERT (update or insert into a table?)

The UPSERT operation either updates or inserts a row in a table, depending if the table already has a row that matches the data: if table t has a row exists that has key X: update t set mystuff... where mykey=X else insert into t…
Mark Harrison
  • 297,451
  • 125
  • 333
  • 465
315
votes
10 answers

How to use RETURNING with ON CONFLICT in PostgreSQL?

I have the following UPSERT in PostgreSQL 9.5: INSERT INTO chats ("user", "contact", "name") VALUES ($1, $2, $3), ($2, $1, NULL) ON CONFLICT("user", "contact") DO NOTHING RETURNING id; If there are no conflicts it…
zola
  • 5,737
  • 8
  • 33
  • 48
226
votes
2 answers

PostgreSQL INSERT ON CONFLICT UPDATE (upsert) use all excluded values

When you are upserting a row (PostgreSQL >= 9.5), and you want the possible INSERT to be exactly the same as the possible UPDATE, you can write it like this: INSERT INTO tablename (id, username, password, level, email) VALUES (1,…
Sebastian
  • 5,471
  • 5
  • 35
  • 53
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
157
votes
12 answers

Use multiple conflict_target in ON CONFLICT clause

I have two columns in table col1, col2, they both are unique indexed (col1 is unique and so is col2). I need at insert into this table, use ON CONFLICT syntax and update other columns, but I can't use both column in conflict_targetclause. It…
Oto Shavadze
  • 40,603
  • 55
  • 152
  • 236
132
votes
11 answers

How to do an upsert with SqlAlchemy?

I have a record that I want to exist in the database if it is not there, and if it is there already (primary key exists) I want the fields to be updated to the current state. This is often called an upsert. The following incomplete code snippet…
Russ
  • 10,835
  • 12
  • 42
  • 57
129
votes
8 answers

SQLite UPSERT / UPDATE OR INSERT

I need to perform UPSERT / INSERT OR UPDATE against a SQLite Database. There is the command INSERT OR REPLACE which in many cases can be useful. But if you want to keep your id's with autoincrement in place because of foreign keys, it does not work…
bgusach
  • 14,527
  • 14
  • 51
  • 68
113
votes
5 answers

SQLite INSERT - ON DUPLICATE KEY UPDATE (UPSERT)

MySQL has something like this: INSERT INTO visits (ip, hits) VALUES ('127.0.0.1', 1) ON DUPLICATE KEY UPDATE hits = hits + 1; As far as I know this feature doesn't exist in SQLite, what I want to know is if there is any way to achive the same…
Alix Axel
  • 151,645
  • 95
  • 393
  • 500
1
2 3
…
81 82