Code covered by the BSD License  

Highlights from
upsert - automatically handles database inserts/updates

4.0

4.0 | 3 ratings Rate this file 12 Downloads (last 30 days) File Size: 4.14 KB File ID: #40080
image thumbnail

upsert - automatically handles database inserts/updates

by

 

30 Jan 2013 (Updated )

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

| Watch this File

File Information
Description

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.

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.

Required Products Database Toolbox
MATLAB release MATLAB 8.0 (R2012b)
Tags for This File   Please login to tag files.
Please login to add a comment or rating.
Comments and Ratings (10)
08 Sep 2013 Benny Leung

By the way, this is a fantastic function!

08 Sep 2013 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

06 Sep 2013 Sven

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.

05 Sep 2013 Sven

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.

05 Sep 2013 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);

05 Sep 2013 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);

05 Sep 2013 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);

15 Jul 2013 Sven

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.

14 Jul 2013 Marcus

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 :(

05 Jun 2013 Harish

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
07 Feb 2013

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

21 Feb 2013

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

17 Jun 2013

Added ability for DATE type fields in primary keys

16 Jul 2013

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

06 Sep 2013

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

Contact us