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 (2023). 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!
버전 게시됨 릴리스 정보

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

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

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

Added ability for DATE type fields in primary keys

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

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