Deleting data from a table

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.

Getting ready

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.

How to do it...

1. Create a transformation.

2. The value to which you will compare the price before deleting will be stored as a named parameter. So create it, name it MAX_PRICE, and set 50 as the default value.

3. Drag to the canvas an Excel Input step to read the Excel file with the list of categories.

4. Drag to the canvas a Get Variables step, to get the named variable as a field named max_price with type Number.

5. After that, add a Database lookup step. Configure it to get the id_category fields based on the category descriptions in the Excel file. So far, the transformation looks like this:

   

   Tip

   For higher volumes it's better to get the variable just once in a separate stream and join the two streams with a Join Rows (cartesian product) step.

6. Select the Database lookup step and do a preview. You should see this:

   

7. Finally, add a Delete step. You will find it under the Output category of steps.

8. Double-click the Delete step, select the outdoor connection, and fill in the key grid as follows:

   

9. Save and run the transformation.

10. Explore the database. If you run the same statements that you ran before starting the recipe, you'll note that all products belonging to the categories in the Excel file, with price less than or equal to $50 have been deleted. This is what you will see:

    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)

How it works...

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

See also

• Looking for a value in a database table (Chapter 5, Looking for Data). Refer to this recipe if you need to understand how the Database lookup step works.