Skip to main content
Pentaho Documentation

Delete

Parent article

The Delete step permanently removes a row from a database so you can cleanse your data. In the Delete step, choose a field to compare against the values of incoming fields from another step. When the comparison requirements are satisfied, the database row is deleted. If multiple rows match, then all rows with that value are deleted from the database.

General

Delete step dialog box

The following table describes the general options for the Delete step.

OptionDescription
Step nameSpecifies the unique name of the Delete step on the canvas. You can customize the name or leave it as the default.
ConnectionSelect the name of a connected database from the drop-down list. Alternately, you can:
  • Click Edit to revise your current database connection.
  • Click New to establish a new database connection.
  • Click Wizard to open a new database connection using the Wizard.
Target schemaSpecify the schema of the table to load from your database.
Target tableSpecify the name of the table in your database where you want to delete the data.
Commit sizeSpecify the size of the commit batch. The size is the number of DELETE statements to perform before sending a COMMIT command to the database.

Depending on your connected database, commit sizes can affect step performance. If blank or set to 0, the database determines the size.

The default is 100.

The key(s) to look up the value(s) table

The columns in the following table define the key(s) used to map the row(s) to delete.

ColumnDescription
Table fieldSpecify the field name from the populated list of Target table columns for which you want to compare.
ComparatorSpecify the comparator you want to use. Note that case-sensitive comparisons are possible, depending on your connected database and Target table.

You can select one of the following comparators:

  • =
  • <>
  • <
  • <=
  • >
  • >=
  • LIKE
  • BETWEEN
  • IS NULL
  • IS NOT NULL
Stream field 1Specify the name of the field from the incoming stream that contains the row you want to use for comparison against the Table field value.
Stream field 2Specify the name of the field from the incoming stream that contains the row you want to use for the BETWEEN comparison against the Stream field 1.
Get fieldsSelect this button to populate Stream field 1 and Stream field 2 from previous steps in your transformation.

For example, if the look up value of QUANTITYORDERED is less than or equal to the min_quantityordered, and PRODUCTLINE values map equally, then that QUANTITYORDERED row is deleted from the STG_SALES_DATA table.

Metadata injection support

All fields of this step support metadata injection. You can use this step with ETL metadata injection to pass metadata to your transformation at runtime.