The "Run query in SQL table" 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 create a ‘Manipulate data’ action step so that you can add and use instructions.
The Managed Table has the following details and design:
1. Create a "Run query in SQL table" instruction
2. Open the SQL Query Editor by clicking the empty query box.
3. In the SQL Query Editor, you can write the query you want to run, as well as specify the Table you want to run the query on.
4. If you wish to save the query, click the "SAVE" button which is on the bottom right-hand corner of the editor. Otherwise, click "Cancel".
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'.
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 the checkbox beside it.
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.