Sometimes you might have to delete data from a table. If the operation to do 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.
In order to follow the recipe, you should download the material for this chapter: a script for creating and loading the database, and an Excel file with the list of categories involved.
After creating the outdoor database and loading data by running the script provided, and before following the recipe you can explore the database. In particular execute the following statement:
SELECT category, count(*) quantity FROM products p, categories c WHERE p.id_category=c.id_category AND price<=50 GROUP BY p.id_category; +---------------+----------+ | category | quantity | +---------------+----------+ | kitchen | 19 | | lights | 14 | | sleeping bags | 5 | | tents | 4 | | tools | 8 | +---------------+----------+ 5 rows in set (0.00 sec) SELECT category, count(*) quantity FROM products p, categories c WHERE p.id_category=c.id_category AND price>50 GROUP BY p.id_category; +---------------+----------+ | category | quantity | +---------------+----------+ | kitchen | 5 | | lights | 1 | | sleeping bags | 1 | | tents | 8 | | tools | 2 | +---------------+----------+ 5 rows in set (0.00 sec)
The highlighted lines above belong to the products that you intend to delete.
MAX_PRICE
, and set 50
as the default value. max_price
with type Number
. id_category
fields based on the category descriptions in the Excel file. So far, the transformation looks like this:SELECT category, count(*) quantity FROM products p, categories c WHERE p.id_category=c.id_category AND price<=50 GROUP BY p.id_category; +---------------+----------+ | category | quantity | +---------------+----------+ | kitchen | 19 | | lights | 14 | | sleeping bags | 5 | +---------------+----------+ 3 rows in set (0.00 sec) SELECT category, count(*) quantity FROM products p, categories c WHERE p.id_category=c.id_category AND price>50 GROUP BY p.id_category; +---------------+----------+ | category | quantity | +---------------+----------+ | kitchen | 5 | | lights | 1 | | sleeping bags | 1 | | tents | 8 | | tools | 2 | +---------------+----------+ 5 rows in set (0.00 sec)
The Delete step allows you to delete rows in a table in a database based on certain conditions. In this case, you intended to delete rows from the table products
where the price was less than or equal to 50, and the category was in a list of categories, so the Delete step is the right choice. This is how it works.
PDI builds a prepared statement for the DELETE
operation. Then, for each row in your stream, PDI binds the values of the row to the variables in the prepared statement.
Let's see it by example. In the transformation you built a stream where each row had a single category and the value for the price.
If you run the transformation with log level Detailed and look at the log, you will see the statement that is executed:
DELETE FROM products WHERE price < ? AND id_category = ?
The WHERE
clause is built based on the conditions you entered in the Delete configuration window. For every row, the values of the fields you typed in the grid—max_price and id_category—are
bound to the question marks in the prepared statement.
Note that the conditions in the Delete step are based on fields in the same table. In this case, as you were provided with category descriptions and the products table does not have the descriptions but the ID for the categories, you had to use an extra step to get that ID: a Database lookup.
Suppose that the first row in the Excel file had the value tents
. As the ID for the category tents
is 4
, the execution of the prepared statement with the values in this row has the same effect as the execution of the following SQL statement:
DELETE FROM products WHERE price < 50 AND id_category = 4