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

バージョン (10.5 KB) 作成者: Sven
If a data row already exists, UPDATE that row! If it doesn't exist, INSERT that row!

ダウンロード 559 件

更新 2015/9/2


UPSERT inserts new and updates old data to a database table
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
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.

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.


Imagine a database table "PHONE_NOS" with data like:
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:
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.


Sven (2022). upsert(conn,tableName,fieldNames,keyFields,data, varargin) (, MATLAB Central File Exchange. 取得済み .

MATLAB リリースの互換性
作成: R2015a
Windows macOS Linux
タグ タグの追加

Community Treasure Hunt

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

Start Hunting!