update (Database Toolbox)

Replace data in database table with data from MATLAB

Syntax

update(conn, 'tab', colnames, exdata, 'whereclause')

Description

update(conn, 'tab', colnames, exdata, 'whereclause') exports data from the MATLAB variable exdata, into the database table tab, via the database connection conn. The variable exdata can be a cell array, numeric matrix, or structure. You do not define the type of data you are exporting; the data is exported in its current MATLAB format. Existing records in the table are replaced as specified by the SQL command whereclause. Specify the 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 colnames.

The status of the AutoCommit flag determines if update automatically commits the data or if a commit is needed. View the AutoCommit flag status for the connection using get and change it using set. Commit the data using commit or issue an SQL commit statement via the exec function. Roll back the data using rollback or issue an SQL rollback statement via the exec function.

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

Remarks

If you get an error, it may be because the table is open in design mode in Access (edit mode for other databases). Close the table in the database and repeat the insert function. For example, the error might be

[Vendor][ODBC Product Driver] The database engine could not lock 
table 'TableName' because it is already in use by another person 
or process.

Examples

Example 1--Update a Record

In the Birthdays table, update the record where First_Name is Jean, replacing the current value for Age with the new value, 40. The connection is conn.

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

```
colnames = {'Age'}
```

Define a cell array containing the new data.

```
exdata(1,1) = {40}
```

Perform the update.

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

Example 2--Update Followed by rollback

This example shows a database update when the AutoCommit flag is off and the data is then rolled back. First set the AutoCommit flag to off for database connection conn.

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

Update the data in the column Date of the Error_Rate table for the record selected by whereclause using data contained in the cell array exdata.

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

The data was written, but not committed.

Roll back the data.

```
rollback(conn)
```

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

See Also
commit, database, insert, rollback, set

unregister versioncolumns