Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

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

ByranCarter12
7 - Meteor

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.

6 REPLIES 6
apathetichell
18 - Pollux

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
7 - Meteor

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
18 - Pollux

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
7 - Meteor

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

 

ByranCarter12_0-1644935562780.png

 

ByranCarter12_1-1644935687749.png

 

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

SoccerTil
8 - Asteroid

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
8 - Asteroid

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

SoccerTil_0-1644942129802.pngSoccerTil_1-1644942160254.png

 

 

Labels