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

Question

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.

JavierSoriaValdes · Answer

Hi, this is my first contribution to the community, but I hope this might work for you or someone in the future who wants to implement something similar.

I wanted to dynamically delete a table in my database using a dynamic database entry, but it always gave me an error.

"Dynamic entry in the database (422) Error opening "SELECT * FROM "DELETE FROM Table WHERE Date = '2024-03-27'"": no column was returned."

Looking at the logic of the internal query that Alteryx executes, it applies a SELECT * FROM "Query" expecting an output, whatever that may be.

How did I solve it? Playing with Alteryx's logic, when expecting a result, just add a generic query (Select 1) to get what Alteryx expects.

"SELECT 1 GO DELETE FROM Table WHERE Date = '2024-03-27'". This first returns a value to Alteryx and then executes the second query.

Hope this helps.

ak2018 · Answer

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.

MichaelLaRose · Answer

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-p/151965

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

Best,

Michael