Changes data in existing rows, either by adding data or by modifying existing data.
update [[database.]owner.]{table_name | view_name}
set [[[database.]owner.]{table_name.|view_name.}]
column_name1 =
{expression1|NULL|(select_statement)}
[, column_name2 =
{expression2|NULL|(select_statement)}]...
[from [[database.]owner.]{view_name|table_name
[(index index_name [ prefetch size ][lru|mru])]}
[,[[database.]owner.]{view_name|table_name
[(index index_name [ prefetch size ][lru|mru])]}]
...]
[where search_conditions]
update [[database.]owner.]{table_name | view_name}
set [[[database.]owner.]{table_name.|view_name.}]
column_name1 =
{expression1|NULL|(select_statement)}
[, column_name2 =
{expression2|NULL|(select_statement)}]...
where current of cursor_name
Component Integration Services processes the update command when the table on which it operates has been created as a proxy table. Component Integration Services forwards the entire request (or part of it) to the server that owns the actual object.
The update command specifies the row or rows you want to change, and the new data. The new data can be a constant, an expression, or data pulled from other tables.
Component Integration Services executes the update command using one of two methods:
The entire command is forwarded to the remote server as a single statement in close to its original syntax. If the syntax and remote capabilities match, the entire statement is forwarded and processed remotely. This is referred to as quickpass mode.
If the entire command cannot be forwarded to a remote server, Component Integration Services declares and opens one or more cursors in update mode, and begins a scan on the remote table. Each cursor forwards as much of the original statement’s predicates to the remote server as possible. For each row fetched that meets the search criteria, a positioned update is executed.
When Component Integration Services forwards the update command to a remote server, the table name used is the remote table name, and the column names used are the remote column names. These names may not be the same as the local proxy table names.
Component Integration Services generally passes the original update syntax to remote servers as a single statement, but the following conditions will likely cause the statement to be executed using method 2, above:
The statement contains multiple tables that are not located in the same remote server.
The statement contains local tables (including temporary tables).
The statement contains certain referential integrity checks.
The statement contains system functions in the predicate list.
The statement contains syntax that the remote server does not support.
The following keywords are ignored and do not prevent Component Integration Services from using quickpass mode:
prefetch
index
lru | mru
The format involving where current of is never forwarded to a remote server and causes the statement to be executed using method 2 above.
If Component Integration Services cannot pass the entire statement to a remote server, a unique index must exist on the table.
The update command is fully supported for all datatypes except text and image. text and image data cannot be changed with the update command, except when setting the text or image value to null. Use the writetext command instead.
If quickpass mode is not used, data is retrieved from the source tables, and the values in the target table are updated using a separate cursor designed for handling a positioned update.
Handling of the update statement is the same as for ASEnterprise.
Handling of the update statement is the same as for ASEnterprise.
The following syntax is supported by servers of class direct_connect:
update [[database.]owner.]{table_name | view_name}
set [[[database.]owner.]{table_name.|view_name.}]
column_name1 =
{expression1|NULL|(select_statement)}
[, column_name2 =
{expression2|NULL|(select_statement)}]...
[where search_conditions]
update commands that conform to this syntax use quickpass mode, if the capabilities response from the remote server indicates that all elements of the command are supported. Examples of negotiable capabilities include: subquery support, group by support, and built-in support.
If the remote server does not support all elements of the command, or the command contains a from clause, Component Integration Services issues a query to obtain the values for the set clause, and then issues an update command to the remote server.
Component Integration Services passes data values as parameters to either a cursor or a dynamic SQL statement. Language statements can also be used if the DirectConnect supports it. The parameters are in the datatype native to Adaptive Server and must be converted by the DirectConnect into formats appropriate for the target DBMS.
The following syntax is supported by servers of class db2:
update [[database.]owner.]{table_name | view_name}
set [[[database.]owner.]{table_name.|view_name.}]
column_name1 =
{expression1|NULL|(select_statement)}
[, column_name2 =
{expression2|NULL|(select_statement)}]...
[where search_conditions]
Servers of class db2 do not contain the capabilities negotiation features of server class direct_connect, so the syntax passed to the remote server is simpler than that allowed by Transact-SQL. The syntax does not contain the following:
Search conditions containing subqueries, group by, or order by clauses
Transact-SQL built-in functions
Transact-SQL operators (such as bitwise operators)
Syntax not allowed by DB2
Component Integration Services processes the update command using method 2, described above, when the statement is complex.
If the server is a DB2 system, use trace flag 11215 to instruct Component Integration Services that the remote server is capable of handling all DB2 syntax. This assumption is not made automatically because not all gateways using the db2 server class are actually connected to DB2 systems. When trace flag 11215 is turned on, quickpass mode is used unless the following conditions exist:
The statement cannot be expressed in DB2 syntax.
The statement contains outer joins.
The statement contains like clauses with Sybase extensions.
The statement contains built-in functions that are not supported by DB2.
When an update statement contains a select statement, Component Integration Services issues a query to obtain the values for the set clause, and then issues an update command to the remote server, unless trace flag 11215 is enabled.
When an update statement contains a from clause, Component Integration Services issues a query to obtain the values for the set clause, and then issues an update command to the remote server.
update in the Reference Manual
