update

Replace data in database table with MATLAB data

Syntax

update(conn,tablename,colnames,data,whereclause)

Description

update(conn,tablename,colnames,data,whereclause) exports the MATLAB^® variable data in its current format into the database table tablename using the database connection conn. You can use the SQL WHERE statement to specify which existing records in the database to replace.

Examples

Update Existing Record Using Cell Array

Connect to a Microsoft^® Access™ database and store the data that you are updating in a cell array. Then, update one column of data in the database table. Close the database connection.

Create the database connection conn to the Microsoft Access database. This code assumes that you are connecting to a data source named dbdemo with a blank user name and password.

conn = database('dbdemo','','');

This database contains the table inventorytable, which contains these columns:

• productnumber

• quantity

• price

• inventorydate

Import all the data from inventorytable as a cell array by using conn, and display the first three rows of imported data.

sqlquery = 'SELECT * FROM inventorytable';
results = fetch(conn,sqlquery,'DataReturnFormat','cellarray');
results(1:3,:)

ans =

  3×4 cell array

    {[1]}    {[1700]}    {[15]}    {'2014-09-23 09:3…'}
    {[2]}    {[1200]}    {[ 9]}    {'2014-07-08 22:5…'}
    {[3]}    {[ 356]}    {[17]}    {'2014-05-14 07:1…'}

Define a cell array containing the name of the column that you are updating.

colnames = {'quantity'};

Define a cell array containing the new data, 2000.

data = {2000};

Update the column quantity in inventorytable for the product with productnumber equal to 1.

tablename = 'inventorytable';
whereclause = 'WHERE productnumber = 1';

update(conn,tablename,colnames,data,whereclause)

Import the data again and view the updated contents in inventorytable.

results = fetch(conn,sqlquery,'DataReturnFormat','cellarray');
results(1:3,:)

ans =

  3×4 cell array

    {[1]}    {[2000]}    {[15]}    {'2014-09-23 09:3…'}
    {[2]}    {[1200]}    {[ 9]}    {'2014-07-08 22:5…'}
    {[3]}    {[ 356]}    {[17]}    {'2014-05-14 07:1…'}

In the inventorytable data, the product with the product number equal to 1 has an updated quantity of 2000 units.

Close the database connection.

close(conn)

Update Existing Record Using Table

Connect to a Microsoft Access database and store the data that you are updating as a table. Then, update multiple columns of data in the database table. Close the database connection.

Create the database connection conn to the Microsoft Access database. This code assumes that you are connecting to a data source named dbdemo with a blank user name and password.

conn = database('dbdemo','','');

This database contains the table inventorytable, which contains these columns:

• productnumber

• quantity

• price

• inventorydate

Import all the data from inventorytable by using conn, and display a few rows of the imported data.

sqlquery = 'SELECT * FROM inventorytable';
results = fetch(conn,sqlquery);
head(results)

ans =

  8×4 table

    productnumber    quantity    price          inventorydate      
    _____________    ________    _____    _________________________

          1            1700       20      '2014-09-23 09:38:34.000'
          2            1200        9      '2014-07-08 22:50:45.000'
          3             356       17      '2014-05-14 07:14:28.000'
          4            2580       21      '2013-06-08 14:24:33.000'
          5            9000        3      '2012-09-14 15:00:25.000'
          6            4540        8      '2013-12-25 19:45:00.000'
          7            6034       16      '2014-08-06 08:38:00.000'
          8            8350        5      '2011-06-18 11:45:35.000'

Define a cell array containing the names of the columns that you are updating in inventorytable.

colnames = {'price','inventorydate'};

Define a table that contains the new data. Update the price to $15 and set the inventory timestamp to '2014-12-01 08:50:15.000'.

data = table(15,{'2014-12-01 08:50:15.000'}, ...
    'VariableNames',{'price','inventorydate'});

Update the columns price and inventorydate in the table inventorytable for the product number equal to 1.

tablename = 'inventorytable';
whereclause = 'WHERE productnumber = 1';

update(conn,tablename,colnames,data,whereclause)

Import the data again and view the updated contents in inventorytable.

results = fetch(conn,sqlquery);
head(results)

ans =

  8×4 table

    productnumber    quantity    price          inventorydate      
    _____________    ________    _____    _________________________

          1            1700       15      '2014-12-01 08:50:15.000'
          2            1200        9      '2014-07-08 22:50:45.000'
          3             356       17      '2014-05-14 07:14:28.000'
          4            2580       21      '2013-06-08 14:24:33.000'
          5            9000        3      '2012-09-14 15:00:25.000'
          6            4540        8      '2013-12-25 19:45:00.000'
          7            6034       16      '2014-08-06 08:38:00.000'
          8            8350        5      '2011-06-18 11:45:35.000'

The product with the product number equal to 1 has an updated price of $15 and timestamp of '2014-12-01 08:50:15.000'.

Close the database connection.

close(conn)

Update Multiple Records with Multiple Conditions

Connect to a Microsoft Access database and store the data that you are updating in a cell array. Then, update multiple records of data in the table by using multiple WHERE clauses. Close the database connection.

Create the database connection conn to the Microsoft Access database. This code assumes that you are connecting to a data source named dbdemo with a blank user name and password.

conn = database('dbdemo','','');

This database contains the table inventorytable, which contains these columns:

• productnumber

• quantity

• price

• inventorydate

Import all the data from inventorytable by using conn, and display the first few rows of imported data.

sqlquery = 'SELECT * FROM inventorytable';
results = fetch(conn,sqlquery);
head(results)

ans =

  8×4 table

    productnumber    quantity    price          inventorydate      
    _____________    ________    _____    _________________________

          1            1700       20      '2014-12-01 08:50:15.000'
          2            1200        9      '2014-07-08 22:50:45.000'
          3             356       17      '2014-05-14 07:14:28.000'
          4            2580       21      '2013-06-08 14:24:33.000'
          5            9000        3      '2012-09-14 15:00:25.000'
          6            4540        8      '2013-12-25 19:45:00.000'
          7            6034       16      '2014-08-06 08:38:00.000'
          8            8350        5      '2011-06-18 11:45:35.000'

Define a cell array containing the name of the column that you are updating.

colnames = {'quantity'};

Define a cell array containing the new data. Update the quantities for two products.

A = 10000;    % new quantity for product number 5
B = 5000;     % new quantity for product number 8

data = {A;B}; % cell array with the new quantities

Update the column quantity in inventorytable for the products with product numbers equal to 5 and 8. Create a cell array whereclause that contains two WHERE clauses, one for each product.

tablename = 'inventorytable';
whereclause = {'WHERE productnumber = 5';'WHERE productnumber = 8'};

update(conn,tablename,colnames,data,whereclause)

Import the data again and view the updated contents in inventorytable.

results = fetch(conn,sqlquery);
head(results)

ans =

  8×4 table

    productnumber    quantity    price          inventorydate      
    _____________    ________    _____    _________________________

          1            1700       20      '2014-12-01 08:50:15.000'
          2            1200        9      '2014-07-08 22:50:45.000'
          3             356       17      '2014-05-14 07:14:28.000'
          4            2580       21      '2013-06-08 14:24:33.000'
          5           10000        3      '2012-09-14 15:00:25.000'
          6            4540        8      '2013-12-25 19:45:00.000'
          7            6034       16      '2014-08-06 08:38:00.000'
          8            5000        5      '2011-06-18 11:45:35.000'

The product with the product number equal to 5 has an updated quantity of 10000 units. The product with the product number equal to 8 has an updated quantity of 5000 units.

Close the database connection.

close(conn)

Update Multiple Columns with Multiple Conditions

Connect to a Microsoft Access database and store the data that you are updating in a cell array. Then, update multiple columns of data in the table by using multiple WHERE clauses. Close the database connection.

Create the database connection conn to the Microsoft Access database. This code assumes that you are connecting to a data source named dbdemo with a blank user name and password.

conn = database('dbdemo','','');

This database contains the table inventorytable, which contains these columns:

• productnumber

• quantity

• price

• inventorydate

Import all the data from inventorytable by using conn, and display the first few rows of imported data.

sqlquery = 'SELECT * FROM inventorytable';
results = fetch(conn,sqlquery);
head(results)

ans =

  8×4 table

    productnumber    quantity    price          inventorydate      
    _____________    ________    _____    _________________________

          1            1700       20      '2014-12-01 08:50:15.000'
          2            1200        9      '2014-07-08 22:50:45.000'
          3             356       17      '2014-05-14 07:14:28.000'
          4            2580       21      '2013-06-08 14:24:33.000'
          5            9000        3      '2012-09-14 15:00:25.000'
          6            4540        8      '2013-12-25 19:45:00.000'
          7            6034       16      '2014-08-06 08:38:00.000'
          8            8350        5      '2011-06-18 11:45:35.000'

Define a cell array containing the names of the columns that you are updating.

colnames = {'quantity','price'};

Define a cell array containing the new data. Update the quantities and prices for two products.

% new quantities and prices for product numbers 5 and 8 
% are separated by a semicolon in the cell array
data = {10000,5.5;9000,10};

Update the columns quantity and price in inventorytable for the products with product numbers equal to 5 and 8. Create a cell array whereclause that contains two WHERE clauses, one for each product.

tablename = 'inventorytable';
whereclause = {'WHERE productnumber = 5';'WHERE productnumber = 8'};

update(conn,tablename,colnames,data,whereclause)

Import the data again and view the updated contents in inventorytable.

results = fetch(conn,sqlquery);
head(results)

ans =

  8×4 table

    productnumber    quantity    price          inventorydate      
    _____________    ________    _____    _________________________

          1            1700       20      '2014-12-01 08:50:15.000'
          2            1200        9      '2014-07-08 22:50:45.000'
          3             356       17      '2014-05-14 07:14:28.000'
          4            2580       21      '2013-06-08 14:24:33.000'
          5           10000        6      '2012-09-14 15:00:25.000'
          6            4540        8      '2013-12-25 19:45:00.000'
          7            6034       16      '2014-08-06 08:38:00.000'
          8            9000       10      '2011-06-18 11:45:35.000'

The product with the product number equal to 5 has an updated quantity of 10000 units and price equal to 6, rounded to the nearest number. The product with the product number equal to 8 has an updated quantity of 9000 units and price equal to 10.

Close the database connection.

close(conn)

Input Arguments

conn — Database connection

connection object

Database connection, specified as an ODBC connection object or JDBC connection object created using the database function.

tablename — Database table name

character vector | string scalar

Database table name, specified as a character vector or string scalar denoting the name of a table in the database.

Example: 'employees'

Data Types: char | string

colnames — Database table column names

cell array of character vectors | string array

Database table column names, specified as a cell array of one or more character vectors or a string array to denote the columns in the existing database table tablename.

Example: {'col1','col2','col3'}

Data Types: cell | string

data — Update data

cell array | numeric matrix | table | structure | dataset

Update data, specified as a cell array, numeric matrix, table, structure, or dataset array.

If you are connecting to a database using a JDBC driver, convert the update data to a supported format before running update. If data contains MATLAB dates, times, or timestamps, use this formatting:

• Dates must be character vectors of the form yyyy-mm-dd.

• Times must be character vectors of the form HH:MM:SS.

• Timestamps must be character vectors of the form yyyy-mm-dd HH:MM:SS.FFF.

The database preference settings NullNumberWrite and NullStringWrite do not apply to this function. If data contains null entries and NaNs, convert these entries to an empty value ''.

• If data is a structure, then field names in the structure must match colnames.

• If data is a table or a dataset array, then the variable names in the table or dataset array must match colnames.

whereclause — SQL WHERE clause

character vector | cell array of character vectors | string scalar | string array

SQL WHERE clause, specified as a character vector or string scalar for one condition or a cell array of character vectors or string array for multiple conditions.

Example: 'WHERE producttable.productnumber = 1'

Data Types: char | cell | string

Tips

• The value of the AutoCommit property in the connection object determines whether update automatically commits the data to the database.

  • To view the AutoCommit value, access it using the connection object; for example, conn.AutoCommit.

  • To set the AutoCommit value, use the corresponding name-value pair argument in the database function.

  • To commit the data to the database, use the commit function or issue an SQL COMMIT statement using the exec function.

  • To roll back the data, use rollback or issue an SQL ROLLBACK statement using the exec function.

• You can use datainsert to add new rows instead of replacing existing data.

• To update multiple records, the number of SQL WHERE clauses in whereclause must match the number of records in data.

• If the order of records in your database is not constant, then you can use values of column names to identify records.

• If this error message appears when your database table is open in edit mode:

  [Vendor][ODBC Product Driver] The database engine could 
  not lock table 'TableName' because it is already in use 
  by another person or process.
  

  Then, close the table and rerun the update function.

• Running the same update operation again can cause this error message to appear.

  ??? Error using ==> database.update
  Error:Commit/Rollback Problems