The term "Upsert" refers to any database

Database

A database is an organized collection of data for one or more purposes, usually in digital form. The data are typically organized to model relevant aspects of reality , in a way that supports processes requiring this information...

 statement, or combination of statements, that inserts a record to a table in a database if the record does not exist or, if the record already exists, updates the existing record. The term upsert is a portmanteau of update and insert and is common slang among database developers.

Upserts in SQL

This is not a standard SQL

SQL

SQL is a programming language designed for managing data in relational database management systems ....

 statement, but it is frequently used to abbreviate the equivalent pseudo-code. The SQL:2003

SQL:2003

SQL:2003 is the fifth revision of the SQL database query language. The latest revision of the standard is SQL:2008.-Summary:The SQL:2003 standard makes minor modifications to all parts of SQL:1999 , and officially introduces a few new features such as:* XML-related features * Window functions* the...

 defines a MERGE statement that provides similar functionality. In MySQL

MySQL

MySQL officially, but also commonly "My Sequel") is a relational database management system that runs as a server providing multi-user access to a number of databases. It is named after developer Michael Widenius' daughter, My...

, UPSERT operations are carried out with the INSERT ... ON DUPLICATE KEY UPDATE (where the row is updated if already inserted) syntax.

Frequently, database operations are performed in a context where multiple agents can perform queries on the same database. If the DBMS does not natively support a version of UPSERT/MERGE, the operation should be wrapped in a transaction to guarantee isolation and atomicity.

Upserts in MongoDB

MongoDB

MongoDB

MongoDB is an open source, high-performance, schema-free, document-oriented database written in the C++ programming language...

provides an atomic upsert operation, which creates a new document by combining the criteria for the update with the fields to change.

Example

Suppose a collection is used to track the number of times each page of a website is viewed. Upserts can be used to avoid "seeding" the collection with all possible pages in advance.

The collection starts off empty:

> db.pages.find
>

On each page view, the page's document is created if it doesn't exist yet and its views are incremented if it does.

> db.pages.update({"_id" : "http://www.example.com"}, {"$inc" : {"views" : 1}}, true)
> db.pages.find
{ "_id" : "http://www.example.com", "views" : 1 }
> db.pages.update({"_id" : "http://www.example.com"}, {"$inc" : {"views" : 1}}, true)
{ "_id" : "http://www.example.com", "views" : 2 }