Suppose a manufacturer informs you about the categories of products that will no longer be available. You don't want to have in your database products something that you will not sell. Then you use PDI to delete them.
- From the Packt website, download the
LUX_discontinued.txtfile. - Create a new transformation.
- With a Text file input step, read the file.
- Preview the file. You will see the following:
- After the Text file input step, add an Add constants step to add a
Stringconstant namedman_codewith valueLUX. - Expand the Output category of steps and drag a Delete step to the canvas.
- Create a hop from the Add constants step to the Delete step.
- Double-click the Delete step. Select
jsas Connection and, as Target table, browse and select products. In the grid add the conditionsman_code = man_codeandpro_theme LIKE category. After the Delete step, add a Write to log step. - Right-click the Delete step and define the error handling just like you did in each of the previous tutorials in this chapter.
- Save the transformation.
- Before running the transformation, open the Database Explorer.
- Under the
jsconnection, locate theproductstable and click Open SQL for [products]. - In the simple SQL editor type:
SELECT pro_theme, pro_name FROM js.products p ORDER BY pro_theme, pro_name;
- Click on Execute. You will see the following result set:
- Close the preview data window and the results of the SQL window.
- Minimize the database explorer window.
- The database explorer is collapsed at the bottom of the Spoon window.
- Run the transformation.
- Look at the Step Metrics. The following is what you should see:
- Maximize the database explorer window.
- In the SQL editor window click Execute again. This time you will see this:
You deleted from the products table all products belonging to the categories found in the LUX_discontinued.txt file.
Note that to query the list of products, you used the PDI Database explorer. You could have done the same by using MySQL Query Browser.
The Delete step allows you to delete records of a database table based on a given condition. For each row coming to the step, PDI deletes the records that match the condition set in its configuration window.
Let's see how it worked in the tutorial. The following is the dataset coming to the Delete step:
For each of these two rows PDI performs a new delete operation.
For the first row, the records deleted from the products table are those where man_code is equal to LUX and pro_theme is like FAMOUS LANDMARKS.
For the second row, the records deleted from the products table are those where man_code is equal to LUX and pro_theme is like COUNTRYSIDE.
You can verify the performed operations by comparing the result sets you got in the database explorer before and after running the transformation.
Just for your information, you could have done the same task with the following DELETE statements:
DELETE FROM products WHERE man_code = 'LUX' and pro_theme LIKE 'FAMOUS LANDMARKS' DELETE FROM products WHERE man_code = 'LUX' and pro_theme LIKE 'COUNTRYSIDE'
In the Step Metrics result, you may notice that the updated column for the Delete step has value 2. This number is the number of delete operations, not the number of deleted records, which was actually a bigger number.