Database Toolbox™

update - Replace data in database table with MATLAB^® data

Syntax

update(conn, 'tab', colnames, exdata, 'whereclause')
update(conn, 'tab', colnames, ...
{datA,datAA,...; datB,datBB,...; datn,datNN}, ...
{'where col1 = val1'; where col2 = val2'; ... 'where  coln = valn'}

Description

update(conn,'tab', colnames, exdata, 'whereclause') exports the MATLAB variable exdata in its current format into the database table tab using the database connection conn.exdata can be a cell array, numeric matrix, or structure. Existing records in the database table are replaced as specified by the SQL whereclause command.

Specify column names for tab as strings in the MATLAB cell array colnames. If exdata is a structure, field names in the structure must exactly match field names in colnames.

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 a SQL commit statement via the exec function. Roll back the data by runningrollback or a SQL rollback statement via the exec function.

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

update(conn, 'tab', colnames, {datA,datAA,...; datB,datBB,...; datn,datNN}, {'where col1 = val1'; where col2 = val2'; ... 'where coln = valn'}) exports multiple records for n where clauses. The number of records in exdata must equal n.

Remarks

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

An error like the following may 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 repeat the update function.

An error like the following may appear if you try to run an update operation that is identical to one that you just ran:
```
??? Error using ==> database.update
Error:Commit/Rollback Problems
```

Examples

Example 1 — Update an Existing Record

Update the record in the Birthdays table using the database connection conn, where First_Name is Jean, replacing the current value for Age with 40.

First define a cell array containing the column name that you are updating, Age.
```
colnames = {'Age'}
```
Define a cell array containing the new data, 40.
```
exdata(1,1) = {40}
```

Run the update.

update(conn, 'Birthdays', colnames, exdata, ... 
	 'where First_Name = ''Jean''')

Example 2 — Roll Back Data after Updating a Record

Update the column Date in the Error_Rate table for the record selected by whereclause, using data contained in the cell array exdata. The AutoCommit flag is off. The data is rolled back after the update operation is run.

Set the AutoCommit flag to off for database connection conn.
```
set(conn, 'AutoCommit', 'off')
```

Update the Date column.

update(conn, 'Error_Rate', {'Date'}, exdata, whereclause)

Because the data was not committed, you can roll it back.
```
rollback(conn)
```

The update is reversed; the data in the table is the same as it was before you ran update.

Example 3 — Update Multiple Records with Different Constraints

Given the table TeamLeagues, where column names are 'Team', 'Zip_Code', and 'New_League':

'Team1'    02116
'Team2'    02138
'Team3'    02116

Assign teams with a zip code of 02116 to the A league and teams with a zip code of 02138 to the B league:

update(conn, 'TeamLeagues', {'League'}, {'A';'B'}, ... 
{'where Zip_Code =''02116''';'where Zip_Code =''02138'''})

update - Replace data in database table with MATLAB® data