Alteryx Designer Knowledge Base

Definitive answers from Designer experts.
Don't forget to submit your entry for the Excellence Awards by October 30! | Need more information about the program? Check out the blog here

Delete Records Using a Post SQL Statement Macro

Alteryx
Alteryx
Created

Sometimes you may need records removed from a database table before adding in new records. This can be done using the Pre SQL Statement in the Input tool, which will run an SQL query before, for instance, appending new records to the table. However, what if you do not want to run the same delete statement every time? For example, let's say that you need to delete all records that match a particular date for every run?

 

This article covers how to delete records from a database using an Input Text tool containing a parameter and a macro using the Post SQL Statement. The macro below is an example of how you can do this. 

  

  1. Create a macro that reads in a date from a Text Input tool from a workflow.

 3-28-2016 12-29-41 PM.png

 

  1. Use the first Action tool to update part of the Post SQL Statement with the date being fed into the macro through the Control Parameter from the workflow. We are updating the ‘x’ as shown below, which is being used as a place holder in the Post SQL Statement, with the date being fed into the macro. 

 step2.png

 

In the Post SQL Statement of the Input tool within the macro, we created the Delete statement with a temporary place holder for the date being read in (‘x’ as shown below). The incoming date will replace the ‘x’ as a result of the Action tool in the previous step.

 

 step22.png

  

 

  1. Use the second Action tool to also update the filter in a smiliar fashion as the input tool:

 3-28-2016 12-32-59 PM.png

 

The filter tool is needed because we are using a post-sql statement for the delete query which means that the tool will first read in the data and then run the delete, so the records to be deleted will still be read in. The filter is added to stop the deleted records from being part of the macro output. 

Important: 
The reason this is set up that way is because if the delete statement were part of the pre-SQL query, it would be executed as soon as the workflow has been opened which may lead to unintentional deletion of records. The post-SQL statement won't execute until after the table has been read in.

  

 

  1. You may also add a macro output for the deleted records to confirm that the correct records were deleted. This will also give you the opportunity to put records back if you didn't mean to delete them as they can be exported from the Alteryx browse window:

 3-28-2016 12-43-22 PM.png

 

 

 

  1. Create a workflow that feeds a date from a Text Input tool into the macro.

 Image 05.png

 

  1. When the workflow is run, whatever date you have entered in the Text Input tool in the workflow will replace the ‘x’ in the macro, deleting the record(s) that match that date.

 

Important: If you change the SQL statement, make sure to make any necessary related changes to the Action tools as well.

 

Comments, suggestions?

Comments
7 - Meteor

Is it possible to create this macro with the "Date Time Now" so that it always looks for current date without requiring a file stored outside the workflow? I tried fiddling with it but could not get the same result. I want to write a delete statement in most post SQL that always runs off current date.