Is there a way to do a delete statement in In-DB

[jeeva_ganesan]

Hi All,

Is there a way to do a delete statement in in-DB alteryx ? I know that we can do "Delete Data and Append" in creation mode of Write Data-InDB. But there is no way to specify the where condition or its values in here. In the normal Non-IndB tools, we could update the pre or post sql statement by placing it in the macro. But when I do the same for In-DB tools, there is no way to specify the SQL statements here. Could someone help on this.

Regards,

Jeeva.

[michael_treadwell]

There is no straightforward way to send a DELETE FROM WHERE command using the In-DB tools. Just to test, I tried using a Text Input and Data Stream In tool to send the command to the DB but it fails when no data is returned and you can't issue two statements semicolon separated.

You could always connect to the table, use Filter In-DB to remove the unwanted records, and then write the rest back using the Write Data In-DB tool.

[RemcoD]

This will only work using 2 Write Data In-DB tools. The first one to write the results into a temporary table, the second to write to the destination table using delete & append. Withouth the temporary table, the records will be deleted and there is nothing to append, because the records aren't loaded in-memory. The result would be an empty table.

[jeeva_ganesan]

This approach will drastically affect the performance as the data load is huge, we cant afford to have the write operation twice. Thats the main reason we have decided to go with In-DB tools. Alteryx should definitely consider this option, this is a basic delete statement with where condition on it, we cannot delete the whole data or filter out and write the whole data in to temp table just to delete few records.

[jeeva_ganesan]

@NeilR  @s_pichaipillai @KaneG @chris_love Can any of you guys shed some light on this ?

[michael_treadwell]

If you don't want to write twice and the data has to go back into the same table, you could use Runtime Events to create a new table before the module runs, follow the steps above with Filter In-DB and a single Write Data In-DB, and then use a Runtime Event to change the name of the tables after a successful run.

[MarqueeCrew]

Are you ok with writing a temp table with keys using in-Db tools followed by a pre-sql action from a called module that uses delete from logic on join to your temp table?

just an airport thought,

Mark

[jeeva_ganesan]

Thanks for the replies. Hi Mark, I already have the approach mentioned by you implemented. Writing the data in to temporary table, getting the keys alone from that and doing pre-sql delete in the macro which has delete statement with these keys. Since it is an output tool in the macro, I cannot tell the outside workflow once the delete is completed, my data append using write -indb in outside workflow has to happen once the delete inside the macro pre-sql is completed. Is there a way to communicate or block till the delete inside the macro completes ?

[MarqueeCrew]

I had created a waiting macro before. It is an iterative macro that is called given the current time plus X minutes. It checks the current time until the defined time. This could help you to delay.

Sent from my iPhone

[jeeva_ganesan]

Oh no, we cannot specify the delay time as we don't know how much time the delete operation take.