update

Replace data in database table with MATLAB data

Syntax

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

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. Existing records in the database table are replaced as specified by the SQL whereclause command.

Examples

expand all

Update an Existing Record

Create a database connection conn using the dbtoolboxdemo data source.

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

Working with the dbtoolboxdemo data source, use fetch to import all data from the inventoryTable. Store the data in a cell array contained in the cursor object field curs.Data.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);

View the contents of the Data element in the cursor object.

curs.Data
ans = 

    [ 1]    [2700]    [14.500000000000000]
    [ 2]    [1700]    [                 9]
    [ 3]    [ 356]    [                17]
    [ 4]    [2580]    [                21]
    [ 5]    [9000]    [                 3]
    [ 6]    [4540]    [                 8]
    [ 7]    [6034]    [                16]
    [ 8]    [8350]    [                 5]
    [ 9]    [2339]    [                13]
    [10]    [ 723]    [                24]
    [11]    [ 567]    [                 0]
    [12]    [1278]    [                 0]
    [13]    [1700]    [14.500000000000000]
    [25]    [ 439]    [                60]
    [25]    [ 439]    [                60]

Data contains the inventoryTable data.

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

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)

Fetch 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.500000000000000]
    [ 2]    [1700]    [                 9]
    [ 3]    [ 356]    [                17]
    [ 4]    [2580]    [                21]
    [ 5]    [9000]    [                 3]
    [ 6]    [4540]    [                 8]
    [ 7]    [6034]    [                16]
    [ 8]    [8350]    [                 5]
    [ 9]    [2339]    [                13]
    [10]    [ 723]    [                24]
    [11]    [ 567]    [                 0]
    [12]    [1278]    [                 0]
    [13]    [1700]    [14.500000000000000]
    [25]    [ 439]    [                60]
    [25]    [ 439]    [                60]

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

After finishing with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Update Multiple Records with Multiple Conditions

Create a database connection conn using the dbtoolboxdemo data source.

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

Working with the dbtoolboxdemo data source, use fetch to import all data from the inventoryTable. Store the data in a cell array contained in the cursor object field curs.Data.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);

View the contents of the Data element in the cursor object.

curs.Data
ans = 

    [ 1]    [2700]    [14.500000000000000]
    [ 2]    [1700]    [                 9]
    [ 3]    [ 356]    [                17]
    [ 4]    [2580]    [                21]
    [ 5]    [9000]    [                 3]
    [ 6]    [4540]    [                 8]
    [ 7]    [6034]    [                16]
    [ 8]    [8350]    [                 5]
    [ 9]    [2339]    [                13]
    [10]    [ 723]    [                24]
    [11]    [ 567]    [                 0]
    [12]    [1278]    [                 0]
    [13]    [1700]    [14.500000000000000]
    [25]    [ 439]    [                60]
    [25]    [ 439]    [                60]

Data contains the inventoryTable data.

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

colnames = {'Quantity'};

Define a cell array containing the new data.

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)

Fetch the data again and view the updated contents in inventoryTable.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [ 1]    [ 2700]    [14.500000000000000]
    [ 2]    [ 1700]    [                 9]
    [ 3]    [  356]    [                17]
    [ 4]    [ 2580]    [                21]
    [ 5]    [10000]    [                 3]
    [ 6]    [ 4540]    [                 8]
    [ 7]    [ 6034]    [                16]
    [ 8]    [ 5000]    [                 5]
    [ 9]    [ 2339]    [                13]
    [10]    [  723]    [                24]
    [11]    [  567]    [                 0]
    [12]    [ 1278]    [                 0]
    [13]    [ 1700]    [14.500000000000000]
    [25]    [  439]    [                60]
    [25]    [  439]    [                60]

In the inventoryTable data, the product with the product number equal to 5 has an updated quantity of 10000 units and the product with the product number equal to 8 has an updated quantity of 5000 units.

After finishing with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Update Multiple Columns with Multiple Conditions

Create a database connection conn using the dbtoolboxdemo data source.

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

Working with the dbtoolboxdemo data source, use fetch to import all data from inventoryTable. Store the data in a cell array contained in the cursor object field curs.Data.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);

View the contents of the Data element in the cursor object.

curs.Data
ans = 

    [ 1]    [2700]    [14.500000000000000]
    [ 2]    [1700]    [                 9]
    [ 3]    [ 356]    [                17]
    [ 4]    [2580]    [                21]
    [ 5]    [9000]    [                 3]
    [ 6]    [4540]    [                 8]
    [ 7]    [6034]    [                16]
    [ 8]    [8350]    [                 5]
    [ 9]    [2339]    [                13]
    [10]    [ 723]    [                24]
    [11]    [ 567]    [                 0]
    [12]    [1278]    [                 0]
    [13]    [1700]    [14.500000000000000]
    [25]    [ 439]    [                60]
    [25]    [ 439]    [                60]

Data contains the inventoryTable data.

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.

% 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)

Fetch the data again and view the updated contents in the inventoryTable.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [ 1]    [ 2700]    [14.500000000000000]
    [ 2]    [ 1700]    [                 9]
    [ 3]    [  356]    [                17]
    [ 4]    [ 2580]    [                21]
    [ 5]    [10000]    [ 5.500000000000000]
    [ 6]    [ 4540]    [                 8]
    [ 7]    [ 6034]    [                16]
    [ 8]    [ 9000]    [                10]
    [ 9]    [ 2339]    [                13]
    [10]    [  723]    [                24]
    [11]    [  567]    [                 0]
    [12]    [ 1278]    [                 0]
    [13]    [ 1700]    [14.500000000000000]
    [25]    [  439]    [                60]
    [25]    [  439]    [                60]

In the inventoryTable data, 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 finishing with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Roll Back Data After Updating a Record

Create a database connection conn. For example, the following code uses the database toy_store, user name username, password pwd, server name sname, and port number 123456 to connect to a Microsoft® SQL Server® database.

conn = database('toy_store','username','pwd',...
                'Vendor','Microsoft SQL Server',...
                'Server','sname',...
                'portnumber',123456);

Set the AutoCommit flag to off. Any updates you make after turning off this flag will not commit to the database automatically.

set(conn,'AutoCommit','off')

Display the data in the inventoryTable table before making updates.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [   1.00]    [ 1700.00]    [ 14.50]
    [   2.00]    [ 1200.00]    [  9.30]
    [   3.00]    [  356.00]    [ 17.20]
    ...

Define a cell array for the new price of the first product.

data(1,1) = {30.00};

Define the WHERE clause for the first product.

whereclause = 'where productNumber = 1';

Update the Price column in the inventoryTable for the first product.

tablename = 'inventoryTable';
colname = {'Price'};

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

Display the data in the inventoryTable table after making the update.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [   1.00]    [ 1700.00]    [ 30.00]
    [   2.00]    [ 1200.00]    [  9.30]
    [   3.00]    [  356.00]    [ 17.20]
    ...

The first product has an updated price of 30.00. Though the data is updated, the change has not committed to the database.

Roll back the update.

rollback(conn)

Display the data in the inventoryTable table after rolling back the update.

curs = exec(conn,'select * from inventoryTable');
curs = fetch(curs);
curs.Data
ans = 

    [   1.00]    [ 1700.00]    [ 14.50]
    [   2.00]    [ 1200.00]    [  9.30]
    [   3.00]    [  356.00]    [ 17.20]
    ...

The first product has the old price of 14.50.

After finishing with the cursor object, close it.

close(curs)

Close the database connection.

close(conn)

Input Arguments

expand all

conn — Database connectionconnection object

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

tablename — Database table namestring

Database table name, specified as a string denoting the name of a table in your database.

Data Types: char

colnames — Database table column namescell array of strings

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

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

Data Types: cell

data — Update datacell array | numeric matrix | structure

Update data, specified as a MATLAB variable with cell array, numeric matrix, or structure format. If data is a structure, field names in the structure must match field names in colnames.

Data Types: double | struct | cell

whereclause — SQL WHERE clausestring | cell array

SQL WHERE clause, specified as a string for one condition or a cell array of strings for multiple conditions.

Example: 'WHERE productTable.productNumber = 1'

Data Types: char

More About

expand all

Tips

  • The status of the AutoCommit flag determines whether update automatically commits the data to the database. View the AutoCommit flag status for the connection using get and change it using set. Commit the data by running commit or an SQL commit statement using the exec function. Roll back the data by running rollback or an SQL rollback statement using the exec function.

  • To add new rows instead of replacing existing data, use fastinsert.

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

  • The order of records in your database is not constant. Use values of column names to identify records.

  • An error like the following might appear if 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.
    

    In this case, close the table and rerun the update function.

  • An error like the following might appear if you try to run an update operation that matches the one that you just ran.

    ??? Error using ==> database.update
    Error:Commit/Rollback Problems
    
Was this topic helpful?