Replace data in database table with MATLAB data
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)
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)
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)
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)
conn — Database connectionconnection objectDatabase connection, specified as a connection object created with the
database function.
tablename — Database table nameDatabase 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 namesDatabase 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 dataUpdate 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 clauseSQL 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
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