In-Database Update and/or Delete

[MarqueeCrew]

DELETE from Source_Data Where ID in

SELECT ID from My_Temp_Table where FLAG = 'Y'

.... 

Essentially, I want to update a DB table with either an update or with the deletion of rows.  I can't delete all of the data.  My work around will be to create/insert into a table the keys that i want to delete and try to use a input/output tool with SQL that performs the delete.  Any other suggestions are welcome, but a tool is best.

Thanks,

Mark

[aser]

We need this functionality too.

It would made a better performance because it runs in-database.

Please add it

[jyeh]

Workaround is to create a macro that would send the SQL as in the Output data tool that overwrites the Pre Create SQL Statement, but the solution is not that elegant as it needs to use some kind of temp table.

Please add functionality and consider even allowing to use a set of pass-through SQL queries to be executed if desired.

[DanHare]

I understand that having SQL passthrough queries would break the data lineage, but we need something that allows data loading and updating routines to reside entirely in an Alteryx workflow.

Also, more complex Stored Procs can't be run currently, and the ability to reference a stored procedure means that there is no transparency on underlying objects anyway.

[SeanAdams]

We spoke to a few of the team at Inspire - and this is one of the few areas where Alteryx is not as feature capable as the competitors.   If we were to be able to add basic DB update & delete as native operators; and support for dimensions and table merges - we'd be able to throw away other ETL tools completely.

Ref @BenG

[DanHare]

Completely agree, and a bump for the inability to connect to more complicated stored procedures...

[MJP]

Is there an estimated release date for the ability to run update queries in-database with Oracle and SQL Server?  The request has been discussed on the site for years, and this is becoming an increasing prominent deficiency as use of Alteryx spreads among developers and database users.

Also, when will the ability to call stored procedures in Oracle be released?

Thanks,

Michael

[deepubcbsnc]

I too prefer some more options in "Write Data In-DB" tool (update: warn on update failure, update: error on update failure and update: insert if new). 

I am having a unique primary index on the table and when i have update: insert if new in the output and we have close to million records, it takes a lot of time, having it in In-DB, should make it much faster. 

Please consider adding these functionalities 

[SeanAdams]

Update on this thread: Many databases (e.g. SQL server) give an output from an insert or a delete which assists with understanding the output. 

For example - the output of a delete can be the number of rows impacted.

So, when we do have a delete tool, it should have:

• an output node
• the ability to pass on control
• The ability to hold flow until the delete is completed

[NicolasL]

The In-Database component don't allow to process a simple UPDATE statement. It require to create a new table etc etc....

And as i'm dealing with 110 000 000 records it's a waste of resource to duplicate in another table... and/or seems stupid to download and manipulate that much of data on the Alteryx server while launching a simple UPDATE statement do the job ...

Anything on the roadmap for this simple need ?

[jaichan]

I am also waiting on this feature which makes life easier. Is this update/insert feature added in new 2018.1 release??