upsert(conn,tableNa​me,fieldNames,keyFi​elds,data, varargin)

Version 1.6.0.0 (10.5 KB) by Sven
If a data row already exists, UPDATE that row! If it doesn't exist, INSERT that row!
560 Downloads
Updated 2 Sep 2015

View License

UPSERT inserts new and updates old data to a database table
UPSERT(CONNECT,TABLENAME,FIELDNAMES,KEYFIELDS,DATA).
CONNECT is a database connection object.
TABLENAME is the database table.
FIELDNAMES is a string array of database column names.
KEYFIELDS is the list of primary key fields that must be matched to
perform an UPDATE rather than an INSERT. It may be given as a logical
array the same length as FIELDNAMES, or a string or cell array of
strings of key column names (in which case KEYFIELDS must be a subset
of FIELDNAMES).
DATA is a MATLAB cell array.
INSERTEDMASK = UPSERT(...) returns a logical vector with one element for
each row of DATA, indicating whether the "upsert" operation meant that
corresponding row of DATA was inserted (TRUE) or merely updated (FALSE).

UPSERT(...,'dateFields',DATEFIELDS) allows a DATE type field to be used
as one of the primary key fields. DATEFIELDS is specified equivalently to
KEYFIELDS. Each primary key DATE type field's data MUST be given as an
ANSI string literal (i.e., '1998-12-25'), rather than a MATLAB datenum
number or a differently formatted date string.
(see http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements003.htm#SQLRF51062)

UPSERT(...,'updateFcn',FUNCTION_HANDLE) optionally allows a user to
provide their own function to replace the default MATLAB "update".

UPSERT(...,'debug',true) prints out diagnostic information.

Note: UPSERT runs in two transactions (an insert, then an update) so is not "atomic" thus shouldn't be used in mission-critical applications or multiple-client systems with concurrent and conflicting transactions. Different database flavours implement (or don't) some kind of upsert (MERGE in Oracle, and ... ON CONFLICT in PostgreSQL) but until they agree on a general solution I've found this is a useful MATLAB tool.

Example:

Imagine a database table "PHONE_NOS" with data like:
PERSONID | TYPE | NUMBER
1 'HOME' 1234567
1 'MOB' 1222222
2 'HOME' 9888888

Then the MATLAB commands:
newNos = {1 'MOB' 4444444
2 'MOB' 5555555};
INS = upsert(conn, 'PHONE_NOS', {'PERSONID','TYPE','NUMBER'}, [1 1 0], newNos)

Would result in the table having contents:
PERSONID | TYPE | NUMBER
1 'HOME' 1234567
1 'MOB' 4444444
2 'HOME' 9888888
2 'MOB' 5555555

The returned variable (INS) would be [0; 1], meaning the second row was
updated, the first row was inserted.

Cite As

Sven (2024). upsert(conn,tableName,fieldNames,keyFields,data, varargin) (https://www.mathworks.com/matlabcentral/fileexchange/40080-upsert-conn-tablename-fieldnames-keyfields-data-varargin), MATLAB Central File Exchange. Retrieved .

MATLAB Release Compatibility
Created with R2015a
Compatible with any release
Platform Compatibility
Windows macOS Linux

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!
Version Published Release Notes
1.6.0.0

Fixed a potential issue with very large integers being rounded due to scientific notation. Now uses direct integer matching if integers supplied.

1.5.0.0

Fixed a bug where the mask [0 1 0] was interpreted numerically rather than [false true false]

1.4.0.0

Fixed single transpose typo whereby upsert of dates would only work one-at-a-time

1.3.0.0

Added ability for DATE type fields in primary keys

1.2.0.0

Another minor fix to the handling of upserting 1000+ rows at a time

1.1.0.0

Fixed typo where last row on data sets over 1000 rows was skipped.

1.0.0.0