DELETE FROM statement

The DELETE FROM statement deletes rows from a table in an external database, based on a search condition.

Syntax

Read syntax diagramSkip visual syntax diagramDELETEFROMTableReference ASCorrelationNameWHEREExpression
WHERE
Read syntax diagramSkip visual syntax diagramTableReference =  Database.DataSourceClause.SchemaClause.TableClause
Read syntax diagramSkip visual syntax diagramDataSourceClause =  DataSourceName{DataSourceExpression}
Read syntax diagramSkip visual syntax diagramSchemaClause =  SchemaName{SchemaExpression}
Read syntax diagramSkip visual syntax diagramTableClause =  TableName{TableExpression}

All rows for which the WHERE clause expression evaluates to TRUE are deleted from 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 retained or deleted according to their contents. The variable is referred to by CorrelationName or, in the absence of an AS clause, by TableName.

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 can contain any of the following elements:
  • 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 can 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 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

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.

If the whole expression cannot be evaluated by the database, the integration node 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 integration node to evaluate the rest. You need to be aware of this situation for two reasons:
  1. 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.
  2. Some databases' functions exhibit subtle differences of behavior from those of the integration node.

Handling errors

It is possible for errors to occur during delete operations. For example, the database might not be operational. 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 has been configured and that the database it identifies has a table called SHAREHOLDINGS, with a column called ACCOUNTNO. 
DELETE FROM Database.SHAREHOLDINGS AS S
       WHERE S.ACCOUNTNO = InputBody.AccountNumber;

This example removes all the rows from the SHAREHOLDINGS table where the value in the ACCOUNTNO column (in the table) is equal to that in the AccountNumber field in the message. This operation might delete zero, one, or more rows from the table.

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

-- Delete rows from the table
DELETE FROM Database.{Source}.{Schema}.{Table} As R WHERE R.Name = 'Joe';