Deleting rows from a table

The Delete Rows action allows you to delete rows from a target table based on a filter and/or a user-defined relationship with another table.

• Use table mapping to define a relationship to one or more lookup tables
• Configure filter options to determine which rows to delete
• Configure record matching options to determine which rows to delete
• Combine logic (And, Or) between the filter options and record matching options

1. Drag to define filter for selecting records.

2. Create filter manually or use Expression Editor.

3. Select delete option based on filtered records.

4. For combined mapping and filter criteria, choose logic.

5. To use table mapping, map lookup table to target table.

6. Select record matching options based on filter and mapping criteria.

7. Review summary of all options.

8. Preview data or SQL statement.

Possible applications include:

• Delete records from a table that meet filter criteria
• Delete records where values are also present (or not present) in a lookup table
• Delete records where values are also present (or not present) in a lookup table, and (or) the records meet a specified filter criteria

Delete rows from a table

1. Drag the Delete Rows icon onto the design surface.
2. On the Connections tab , enter a Name and a Description to identify the action, then specify thetarget database and table; you can also optionally choose one or more lookup tables:

   Target -

   • For Database Connection, select the database that contains the rows to be deleted, or choose New Database Connection to establish a new connection to another database.
   • For Table Name, select the table containing the rows to be deleted.

   Lookup (optional) -

   • For Table Name, choose one or more lookup tables to be checked for missing column values.
3. On the Configuration tab :
   • Use the Filter area to build an expression that identifies records with specific values for selected fields. Expressions can be created directly in the Filter area, or you can use the Expression Editor.
   • Select the Delete records option –
     • Delete all records – All records in the table are deleted.
     • Matching the filter – Only those records that match the filter are deleted.
     • Not matching the filter – Only those records that do not match the filter are deleted.
   • If you are using lookup tables as additional criteria for determining if rows should be deleted, create the necessary field relationship(s) between the target and lookup tables in the Table Mapping area. Drag from a field in the Target table, which contains the rows to be considered, to a field in the Lookup table, which contains rows to be searched. Repeat for all fields being mapped between the two tables, and for additional lookup tables.
     • Once the fields are mapped, use the statement in Record Matching Options to indicate how the mapping determines when records are deleted.

     

     • Choose between any table mapping and every table mapping. In this case, any means a minimum of 1, and every means all.
   • Choose whether row values are or are not found in the lookup table(s).

     For example, suppose your selections result in the following statement -

     “Delete records where, for every table mapping, row values are found in Lookup.”

     In this case, if values are found in the mapped field of every row, then delete the records.

   • If you are including a lookup, use the options available in Combine Logic to determine how the filter and the lookup criteria are to be used together. You can select And or Or. The choice you make here is reflected in the Configuration Options Summary.

   

   • Click Preview to view the data or the SQL statement created from this action. Note that, in addition to viewing the resulting SQL statement, two additional tabs allow you to view remaining rows and deleted rows separately. Refer to Preview for information.

Delete Rows data example

In the following example, a lookup table is used in combination with appropriate logic to delete rows from an input table for which a product value is not found: :