update
Replace data in database table with MATLAB data
Description
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
string scalar | character vector
Database table name, specified as a string scalar or character vector denoting the name of a table in the database.
Example: "employees"
Data Types: string
| char
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 NaN
s, convert these entries to an empty value ''
.
If
data
is a structure, then field names in the structure must matchcolnames
.If
data
is a table or a dataset array, then the variable names in the table or dataset array must matchcolnames
.
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 theconnection
object determines whetherupdate
automatically commits the data to the database.To view the
AutoCommit
value, access it using theconnection
object; for example,conn.AutoCommit
.To set the
AutoCommit
value, use the corresponding name-value pair argument in thedatabase
function.To commit the data to the database, use the
commit
function or issue an SQLCOMMIT
statement using theexec
function.To roll back the data, use
rollback
or issue an SQLROLLBACK
statement using theexec
function.
You can use
datainsert
to add new rows instead of replacing existing data.To update multiple records, the number of SQL
WHERE
clauses inwhereclause
must match the number of records indata
.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
Version History
Introduced before R2006a
MATLAB Command
You clicked a link that corresponds to this MATLAB command:
Run the command by entering it in the MATLAB Command Window. Web browsers do not support MATLAB commands.
Select a Web Site
Choose a web site to get translated content where available and see local events and offers. Based on your location, we recommend that you select: .
You can also select a web site from the following list:
How to Get Best Site Performance
Select the China site (in Chinese or English) for best site performance. Other MathWorks country sites are not optimized for visits from your location.
Americas
- América Latina (Español)
- Canada (English)
- United States (English)
Europe
- Belgium (English)
- Denmark (English)
- Deutschland (Deutsch)
- España (Español)
- Finland (English)
- France (Français)
- Ireland (English)
- Italia (Italiano)
- Luxembourg (English)
- Netherlands (English)
- Norway (English)
- Österreich (Deutsch)
- Portugal (English)
- Sweden (English)
- Switzerland
- United Kingdom (English)