Delete Records Using a Post SQL Statement Macro

[DanC]

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.

2. 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.
    

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.

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

The filter tool is needed because we are using a post-sql statement for the delete query which means that the tool willfirstread in the data andthenrun 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.

4. 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:

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

6. 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 tomake any necessary related changes to the Action tools as well.

Comments, suggestions?

[mdgajes1]

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.

[pjandliz]

This example looks like it takes a dataset then splits it into 'deleted' and 'not deleted' records.

How would you do this if you wanted the macro to delete the records from a data source (SQL) table before appending new r(replacement) records created by the workflow?

I've set up a batch macro like this, with a Pre-SQL dynamic criteria DELETE then appending replacement records to the SQL table.

It all runs without error, however the records I'm trying to delete don't get deleted so an example on this scenario would be most welcomed.

[TerriLH]

I have the same problem. The macro splits the data into the 2 records (delete/keep) but does not remove the ddata from the SQL table. I notice nothing was ever posted in response to this. Is there a fix?

[TerriLH]

This only works to show me the records that I want to remove. It doesn't actually delete the records from the SQL table. I need to be able to delete the records from the table.