Pentaho Data Integration Cookbook Second Edition

Deleting data from a table

Sometimes you might have to delete data from a table. If the operation to do it is simple, for example:

DELETE FROM LOG_TABLE WHERE VALID='N'

Or

DELETE FROM TMP_TABLE

You could simply execute it by using an SQL job entry or an Execute SQL script step. If you face the second of the above situations, you can even use a Truncate table job entry.

For more complex situations, you should use the Delete step. Let's suppose the following situation: you have a database with outdoor products. Each product belongs to a category: tools, tents, sleeping bags, and so on. Now you want to delete all the products for a given list of categories, where the price is less than or equal to $50.

Getting ready

In order to follow the recipe, you should ...