Delete rows to a Database using alteryx (looking for fast method)

[ByranCarter12]

Hello, 

I am working with a workflow to find duplicates from a database and now I am trying to Delete thoose duplicates, directly from alteryx. 

I know there is a PreSQL  Statement to create a query. So, I have create my query, then add a control parameter for build a macro.

I connect my workflow to the macro to delete rows from it, It's working but it's very very long. 

To give you an idea, I have 500 rows to delete, and it's still running after 1 hour (34% complete). 

So, I would like to know if there is a faster method, because it's not possible to use this method. 

Especially , I only have 500 rows, I can't even imagine with millions of rows.

Thank for your help.

[apathetichell]

I'm not quite seeing what you are doing - but it sounds a bit off.... Can you sketch out more of what you are doing? Are you deleting the rows in a SQL database? what's the format? I don't know what the macro is for.

[ByranCarter12]

Yes, I want to delete specific rows in SQL DataBase, using a where condition on a ID, to choose the rows that i want to delete. 

That's why I am using a macro to replace the ID from the query with a control parameter.

I am using exactly the method found in this post :

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Delete-Records-Using-a-Post-SQL-Sta...

[apathetichell]

o.k. - post your action tool configuration screens with samples of the values you are feeding in.

Can you clarify what kind of SQL DB you are using?

[ByranCarter12]

I am using a Postgre DB. I have made some screens of my workflows.

As I've said, it's working but the execution is very very long.

[SoccerTil]

My recommendation for this type of processing is to create a stream with the to-be-deleted IDs and insert them into a database table that will be used to power a SQL Delete with Join statement as the post-SQL statement to delete the records required. This will then become a SQL bulk delete that will perform much more efficiently. This is an example of a MySQL delete statement.

Delete c
From Customer c
Join CustomerDeleteTemp ct
On c.customerID = ct.customerID

This video might help.

https://youtu.be/MABxau-26C0

[SoccerTil]

This is the simple workflow. Text Data has the to-be-deleted ID. And Ouput Data has the Post-SQL.