The UPDATE statement changes the values of specified columns, in selected rows, in a table in an external database.
>>-UPDATE--TableReference--+---------------------+--------------> '-AS--CorrelationName-' .-,-------------------. V | >--SET----Column = Expression-+--+-------------------+--------->< '-WHERE--Expression-' WHERE |--TableReference = --Database---------------------------------> >--+------------------------------------------+-----------------> '-+---------------------+--.--SchemaClause-' '-.--DataSourceClause-' >--.--TableClause-----------------------------------------------| |--DataSourceClause = --+-DataSourceName-------------+---------| '-{--DataSourceExpression--}-' |--SchemaClause = --+-SchemaName-------------+-----------------| '-{--SchemaExpression--}-' |--TableClause = --+-TableName-------------+-------------------| '-{--TableExpression--}-'
If a schema name is not specified, the default schema for the integration node'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 expression can use any of the integration node'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 integration node 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.
It is possible for errors to occur during update operations. For example, the database might not be operational, or the table might 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.
UPDATE Database.StockPrices AS SP
SET PRICE = InputBody.Message.StockPrice
WHERE SP.COMPANY = InputBody.Message.Company
UPDATE Database.INVENTORY AS INV
SET QUANTITY = INV.QUANTITY - InputBody.Message.QuantitySold
WHERE INV.ITEMNUMBER = InputBody.Message.ItemNumber
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.
-- 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;