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 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, explore the database before following the recipe. In particular, execute the following statement:

SELECT  category
   , COUNT(*) quantity
FROM     products p
JOIN     categories c ON p.id_category=c.id_category
WHERE    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
JOIN categories c ON p.id_category=c.id_category
WHERE    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 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 right-click within the transformation and select Transformation settings. Switch to the Parameters tab and create a parameter named MAX_PRICE. Set the default value to 50.

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 the following:

   

   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 the following:

   

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

8. Double-click on 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. The following is what you will see:

    SELECT  category
       , COUNT(*) quantity
    FROM     products p
    JOIN     categories c ON p.id_category=c.id_category
    WHERE    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
    JOIN   categories c ON p.id_category=c.id_category
    WHERE    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 the 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 that 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

• The Looking for values in a database table recipe in Chapter 6, Looking for Data