File Exchange

image thumbnail

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

version 1.6 (10.5 KB) by

If a data row already exists, UPDATE that row! If it doesn't exist, INSERT that row!

0 Downloads

Updated

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.

Comments and Ratings (10)

Benny Leung

By the way, this is a fantastic function!

Benny Leung

Hi Sven,

Can you give me some insights as to why i am getting this error?

Error using database/fastinsert (line 259)
Java exception occurred:
sun.jdbc.odbc.JdbcOdbcBatchUpdateException: [Microsoft][ODBC SQL Server
Driver][SQL Server]Violation of PRIMARY KEY constraint 'PK_CAISO_daLMP'. Cannot
insert duplicate key in object 'dbo.CAISO_daLMP'.

at sun.jdbc.odbc.JdbcOdbcPreparedStatement.emulateExecuteBatch(Unknown
Source)

at sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeBatchUpdate(Unknown
Source)

at sun.jdbc.odbc.JdbcOdbcStatement.executeBatch(Unknown Source)

Error in upsert (line 165)
insertFcn(conn,tableName,fieldNames,data(insertMask,:));
Error in upsert (line 66)
insertMask(dataInds) = ...
Error in CRRdb/daLMP (line 87)
daLMP = upsert(dbConn,tableName,daLMP.Properties.VarNames,[1 1 1 0 0
0],data4Load);

Benny

Sven

Sven (view profile)

Benny, you found an error in my example. [1 1 0] should have been written as [true true false]. I've submitted a fix now that means upsert will now work with either syntax.

Sven

Sven (view profile)

Benny, please include the syntax you used to call upsert.

I have a feeling that either your "keyFields" is empty, or wasn't included in the call to upsert.

Benny Leung

can someone please give me a hand on this? i got the following error

Subscript indices must either be real positive integers or logicals.

Error in upsert (line 111)
keyFieldsCell = fieldNames(keyFields);

Benny Leung

can someone please give me a hand on this? i got the following error

Subscript indices must either be real positive integers or logicals.

Error in upsert (line 111)
keyFieldsCell = fieldNames(keyFields);

Benny Leung

can someone please give me a hand on this? i got the following error

Subscript indices must either be real positive integers or logicals.

Error in upsert (line 111)
keyFieldsCell = fieldNames(keyFields);

Sven

Sven (view profile)

Marcus/Harish, here's a fully worked example:

--- ORACLE SCRIPT ---
DROP TABLE TMP_TABLE CASCADE CONSTRAINTS;
CREATE TABLE TMP_TABLE
(
COL1 NUMBER,
COL2 DATE
);
Insert into TMP_TABLE Values (1, DATE '2013-05-15');
Insert into TMP_TABLE Values (2, DATE '2013-01-01');
Insert into TMP_TABLE Values (3, DATE '2013-01-01');
Insert into TMP_TABLE Values (4, DATE '2013-10-25');
commit work;

--- MATLAB ---

>> D = fetch(conn,'select * from tmp_table')
D =
[1] '2013-05-15 00:00:00.0'
[2] '2013-01-01 00:00:00.0'
[3] '2013-01-01 00:00:00.0'
[4] '2013-10-25 00:00:00.0'

>> newData = {5 '2014-02-07'; 99 '2013-01-01'}
newData =
[ 5] '2014-02-07'
[99] '2013-01-01'

>> upsert(conn, 'tmp_table', {'col1','col2'}, 'col2', newData, 'dateFields', 'col2')

ans =
1
0

>> D = fetch(conn,'select * from tmp_table')
D =
[ 5] '2014-02-07 00:00:00.0'
[ 1] '2013-05-15 00:00:00.0'
[99] '2013-01-01 00:00:00.0'
[99] '2013-01-01 00:00:00.0'
[ 4] '2013-10-25 00:00:00.0'

You see above that the 2014-02-07 date was new, so it was added. The 2013-01-01 date matched two rows, so they were both updated.

Note that I just found a typo in the first file I uploaded with date functionality... it would have failed if newData contained more than 1 row... there's a new one (with a one-character typo changed!) in submission now.

Marcus

Marcus (view profile)

Possible to include an example of how to use the newly added Date type fields in primary key? I have difficulty figuring out how to use it :(

Harish

Harish (view profile)

great utility. It would be even greater if it could handle dates as well. i.e. having a primary of dates wouldnt work in this setting

Updates

1.6

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

1.5

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

1.4

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

1.3

Added ability for DATE type fields in primary keys

1.2

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

1.1

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

MATLAB Release
MATLAB 8.5 (R2015a)

Download apps, toolboxes, and other File Exchange content using Add-On Explorer in MATLAB.

» Watch video