The term "Upsert" refers to any database 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.
Also used in Microsoft SQL Azure.
Contents |
This is not a standard SQL statement, but it is frequently used to abbreviate the equivalent pseudo-code. The SQL:2003 defines a MERGE statement that provides similar functionality. In MySQL, 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.
MongoDB provides an atomic upsert operation, which creates a new document by combining the criteria for the update with the fields to change.
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 }