UPSERT To Database - A Great But Not Common DBMS Feature

UPSERT is the combination of update and insert. The idea behind it is that if you are trying to update an item from a database but doesn't exist then insert it.

So how does this benefit developers?

For one thing, you only have to write one feature to store and update the database promoting code consistency and efficiency. In addition, since it's only one feature you can potentially save transactions to the database for applications that require checking the database prior to updating or inserting. Which is exactly what we want in terms of optimization and it just makes things simpler so developers can focus more on "business" stuff. Moreover, since it is just one statement, it reduces the amount of log noise and chances of errors usually encountered when storing duplicate items or trying to update missing items which is altogether good for replication and system stability.

This paradigm though isn't that popular and only a few databases support it natively. There are however workaround in many relational databases like use of PL/SQL, STORED FUNCTION and STORED PROCEDURES that can be grouped into transactions.

See below (Please post a comment if you think I missed something).

1. MongoDb
2. SQL Server 2003+ (Using MERGE)
3. Oracle 9+ (Using MERGE)

SQL Example:

MERGE INTO [TABLE TARGET] AS TARGET
 USING [TABLE SOURCE] AS SOURCE
 ON [CONDITION]
 WHEN MATCHED THEN 
 UPDATE SET [TARGET COL1= SOURCE VALUE1],...[TARGET COLN= SOURCE VALUEN]
 WHEN NOT MATCHED THEN
 INSERT [FIELDS] VALUES ([VALUES]);

4. MySQL (Using REPLACE INTO or INSERT IGNORE)

The MySQL REPLACE INTO statement doesn't however perform the full concept since it can only do full inserts and full updates/replacement. It cannot do partial updates.

Related Links:

• Related Links Widget for Blogspot