The run query instruction allows a user to run a query on a Managed Table. The instruction can be configured to check whether the number of rows it modifies satisfies a particular condition.
For the purposes of this article we are going to use a simple and fictitious Managed Table named “Airports”. You will want to replace this Table with one of your own
Before we get started
Please further create a ‘Manipulate a file’ or ‘Multiple file manipulations’ action step so that you can add and use instructions.
The Managed Table has the following details and design:
1. Create a ‘Run query’ instruction.
2. Open the SQL Editor by clicking the ‘View/Edit SQL’ hyperlink.
3. In the SQL Editor, you can write the query you want to run, as well as specify the Table you want to run the query on. For example, below is a query which updates the country column of the Airports Table such that any record with the value 'AU' will be changed to 'Australia'.
4. If you wish to save the query. Click the Save and Close button.
5. If you wish to ensure that a certain number of rows were changed follow step 6-8, otherwise skip to step 9.
6. Enable the ‘Check the numbers of rows modified to determine if this step was successful’ rule by ticking its box.
7. The first part of the ‘This step is successful if the number of modified rows’ condition is set to ‘Is exactly’ by default. Other options include ‘Is more than’ and ‘Is less than’.
8. The number of rows that should be changed can be entered into the box on the right.
9. Save and run the step.