image thumbnail

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!

553 Downloads

Updated 02 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 (2021). 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!