The UPDATE statement changes the values of specified
columns, in selected rows, in a table in an external database.
All rows for which the WHERE clause expression
evaluates to TRUE are updated in the table identified by TableReference.
Each row is examined in turn and a variable is set to point to the current
row. Typically, the WHERE clause expression uses this variable to access column
values and thus cause rows to be updated, or retained unchanged, according
to their contents. The variable is referred to by CorrelationName or,
in the absence of an AS clause, by TableName. When a row
has been selected for updating, each column named in the SET clause is given
a new value as determined by the corresponding expression. These expressions
can, if you wish, refer to the current row variable.
Table reference
A table reference is
a special case of the field references that are used to refer to message trees.
It always starts with the word
"Database" and may contain any of the
following:
- A table name only
- A schema name and a table name
- A data source name (that is, the name of a database instance), a schema
name, and a table name
In each case, the name may be specified directly or by an expression
enclosed in braces ({...}). A directly-specified data source, schema, or table
name is subject to name substitution. That is, if the name used has been declared
to be a known name, the value of the declared name is used rather than the
name itself (see
DECLARE statement).
If a schema name
is not specified, the default schema for the broker's database user is used.
If
a data source name is not specified, the database pointed to by the node's data
source attribute is used.
The WHERE clause
The WHERE
clause expression can use any of the broker’s operators and functions in any
combination. It can refer to table columns, message fields, and any declared
variables or constants.
However, be aware that the broker
treats the WHERE clause expression by examining the expression and deciding
whether the whole expression can be evaluated by the database. If it can,
it is given to the database. In order to be evaluated by the database, it
must use only those functions and operators supported by the database.
The WHERE clause can, however, refer to message fields, correlation
names declared by containing SELECT functions, and to any other
declared variables or constants within scope.
If the whole
expression cannot be evaluated by the database, the broker looks for top-level
AND operators and examines each sub-expression separately. It then attempts
to give the database those sub-expressions that it can evaluate, leaving the
broker to evaluate the rest. You need to be aware of this situation for two
reasons:
- Apparently trivial changes to WHERE clause expressions can have large
effects on performance. You can determine how much of the expression was given
to the database by examining a user trace.
- Some databases’ functions exhibit subtle differences of behavior from
those of the broker.
Handling errors
It is possible
for errors to occur during update operations. For example, the database may
not be operational, or the table may have constraints defined that the new
values would violate. In these cases, an exception is thrown (unless the node
has its throw exception on database error property set to
FALSE). These exceptions set appropriate SQL code, state, native error, and
error text values and can be dealt with by error handlers (see the DECLARE
HANDLER statement).
For further information about handling database
errors, see Capturing database state.
Examples
The following
example assumes that the
dataSource property of the Database
node has been configured, and that the database it identifies has a table
called STOCKPRICES, with columns called COMPANY and PRICES. It updates the
PRICE column of the rows in the STOCKPRICES table whose COMPANY column matches
the value given in the Company field in the message.
UPDATE Database.StockPrices AS SP
SET PRICE = InputBody.Message.StockPrice
WHERE SP.COMPANY = InputBody.Message.Company
In
the following example (which make similar assumptions), the SET clause expression
refers to the existing value of a column and thus decrements the value by
an amount in the message:
UPDATE Database.INVENTORY AS INV
SET QUANTITY = INV.QUANTITY - InputBody.Message.QuantitySold
WHERE INV.ITEMNUMBER = InputBody.Message.ItemNumber
The
following example updates multiple columns:
UPDATE Database.table AS T
SET column1 = T.column1+1,
column2 = T.column2+2;
Note that the
column names (on the left of the "=") are single identifiers. They must not
be qualified with a table name or correlation name. In contrast, the references
to database columns in the expressions (to the right of the "=") must be qualified
with the correlation name.
The next example shows the
use of calculated data source, schema, and table names:
-- Declare variables to hold the data source, schema and table names
-- and set their default values
DECLARE Source CHARACTER 'Production';
DECLARE Schema CHARACTER 'db2admin';
DECLARE Table CHARACTER 'DynamicTable1';
-- Code which calculates their actual values comes here
-- Update rows in the table
UPDATE Database.{Source}.{Schema}.{Table} AS R SET Value = 0;