Documentation

This is machine translation

Translated by Microsoft
Mouseover text to see original. Click the button below to return to the English verison of the page.

Note: This page has been translated by MathWorks. Please click here
To view all translated materals including this page, select Japan from the country navigator on the bottom of this page.

update

Replace data in database table with MATLAB data

Syntax

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

Description

example

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

collapse all

First, connect to a Microsoft® Access™ database. 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 a database connection conn to the Microsoft Access database. This code assumes that you are connecting to a data source named dbdemo with blank user name and password.

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

This database contains the table inventoryTable that contains these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

Import all data from the inventoryTable using conn. Store the data in a cell array contained in the Data property of the cursor object. Display the data from inventoryTable in this property.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [ 1]    [1700]    [14.5000]    '2014-09-23 09:38...'
    [ 2]    [1200]    [      9]    '2014-07-08 22:50...'
    [ 3]    [ 356]    [     17]    '2014-05-14 07:14...'
    ...

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

colnames = {'Quantity'};

Define a cell array containing the new data 2000.

data = {2000};

Update the column Quantity in the 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 the inventoryTable.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [ 1]    [2000]    [14.5000]    '2014-09-23 09:38...'
    [ 2]    [1200]    [      9]    '2014-07-08 22:50...'
    [ 3]    [ 356]    [     17]    '2014-05-14 07:14...'
    ...

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

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

First, connect to a Microsoft Access database. 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 a database connection conn to the Microsoft Access database. This code assumes that you are connecting to a data source named dbdemo with blank user name and password.

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

This database contains the table inventoryTable that contains these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

Import all data from the inventoryTable using conn. Store the data in a cell array contained in the cursor object property Data. Display the data from inventoryTable in this property.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [ 1]    [1700]    [14.5000]    '2014-09-23 09:38...'
    [ 2]    [1200]    [      9]    '2014-07-08 22:50...'
    [ 3]    [ 356]    [     17]    '2014-05-14 07:14...'
    ...

Define a cell array containing the column names that you are updating in inventoryTable.

colnames = {'Price','inventoryDate'};

Define a table that contains the data for insertion. Update the price to $15 and set the inventory timestamp to '2014-12-01 8:50:15.0'.

data = table(15,{'2014-12-01 8:50:15.0'},...
             '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 the inventoryTable.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [ 1]    [1700]    [     15]    '2014-12-01 08:50...'
    [ 2]    [1200]    [      9]    '2014-07-08 22:50...'
    [ 3]    [ 356]    [     17]    '2014-05-14 07:14...'
    ...

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

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

First, connect to a Microsoft Access database. Store the data that you are updating in a cell array. Then, update multiple records of data in the table using multiple WHERE clauses. Close the database connection.

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

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

This database contains the table inventoryTable that contains these columns:

  • productNumber

  • Quantity

  • Price

  • inventoryDate

Import all data from the inventoryTable using conn. Store the data in a cell array contained in the Data property of the cursor object. Display the data from inventoryTable in this property.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    ...
    [ 5]    [9000]    [      3]    '2012-09-14 15:00...'
    [ 6]    [4540]    [      8]    '2013-12-25 19:45...'
    [ 7]    [6034]    [     16]    '2014-08-06 08:38...'
    [ 8]    [8350]    [      5]    '2011-06-18 11:45...'
    ...

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

colnames = {'Quantity'};

Define a cell array containing the new data. Update 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 the inventoryTable for the products with product numbers equal to 5 and 8. Create a cell array whereclause that contains two WHERE clauses for both products.

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.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    ...
    [ 5]    [10000]    [      3]    '2012-09-14 15:00...'
    [ 6]    [ 4540]    [      8]    '2013-12-25 19:45...'
    [ 7]    [ 6034]    [     16]    '2014-08-06 08:38...'
    [ 8]    [ 5000]    [      5]    '2011-06-18 11:45...'
    ... 

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.

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

First, connect to a Microsoft Access database. Store the data that you are updating in a cell array. Then, update multiple columns of data in the table using multiple WHERE clauses. Close the database connection.

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

  • productNumber

  • Quantity

  • Price

  • inventoryDate

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

Import all data from inventoryTable using conn. Store the data in a cell array contained in the Data property of the cursor object. Display the data from inventoryTable in this property.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    ...
    [ 5]    [9000]    [      3]    '2012-09-14 15:00...'
    [ 6]    [4540]    [      8]    '2013-12-25 19:45...'
    [ 7]    [6034]    [     16]    '2014-08-06 08:38...'
    [ 8]    [8350]    [      5]    '2011-06-18 11:45...'
    ...

Define a cell array containing the column names that you are updating called Quantity and Price.

colnames = {'Quantity','Price'};

Define a cell array containing the new data. Update 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 the inventoryTable for the products with product numbers equal to 5 and 8. Create a cell array whereclause that contains two WHERE clauses for both products.

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 the inventoryTable.

curs = exec(conn,'SELECT * FROM inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    ... 
    [ 5]    [10000]    [ 5.5000]    '2012-09-14 15:00...'
    [ 6]    [ 4540]    [      8]    '2013-12-25 19:45...'
    [ 7]    [ 6034]    [     16]    '2014-08-06 08:38...'
    [ 8]    [ 9000]    [     10]    '2011-06-18 11:45...'
    ...

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

After you finish working with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Input Arguments

collapse all

Database connection, specified as a connection object created using the database function.

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

Data Types: char | string

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

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

Data Types: cell | string

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.

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
    

Introduced before R2006a

Was this topic helpful?