How do I delete rows from an SQL table using alteryx?

[AkisM]

I thought all I had to do was to drag in an input tool and in "SQL query" put

DELETE * From TableName

,but it gives an error. Is my query wrong or maybe this shouldn't be on the input tool? I'm guessing you can have only read/select statements on the input tool for SQL queries? Does this mean I'm gonna need an output tool to put and put my delete query there? Would appreciate it if someone could attach a sample workflow.

[MichaelLaRose]

Hi @AkisM ,

Alteryx typically only supports read and write statements at all so the input and output tools aren't going to be the change you need.

However, if you are using a database that supports stored procedure calls, or if you can create custom functions, Alteryx should be able to pass the values in your workflow as parameters into those calls in order to delete, drop, etc.

Best,

Michael

[AkisM]

@MichaelLaRose  But there has to be some sort of workaround to that right? For example the way I achieved what i was trying to do in this case was to choose "deleted data & append" in my output options, except there was an error during the append, so the table was left empty (as intended).

Maybe overwrite the table with a workflow that sends 0 rows? Or append 0 rows? Was hoping for a workflow that works around that with something along those lines, since the deletion I'm looking to do isn't complex or based on conditions, just delete all rows from a table.

[MichaelLaRose]

I think you may want to actually just use the Post SQL statement, I didn't realize that you always would be simply dropping the data from the table. 

https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Alteryx-Pre-Post-SQL-Statements/ta-...

I do not remember if there are database restrictions on this functionality.

Best,

Michael

[ak2018]

The challenge is when you want to selectively delete, Say you have newer data and want to delete the old one. The better practice is to make sure you have the right KEYS on the table, so that when you use the OUTPUT TOOL, choose UPDATE, Insert If New option and identify the keys. That way it internally should issue an update statement for existing rows and insert for the new ones. I hope Alteryx has performance tuned with any given database since I would believe this to be quite intensive outside of a Database. 

If your table is not big enough, the best option if you are updating and inserting data is to retrieve that whole table, replace the ones you want to update within Alteryx, UNION with the new ones and use the output tool with an INSERT with Delete old data. 

Hope this helps.