Dropping lines of data in a SQL table?

[zachwalz]

Hey all--

Is there any smooth/easy way to drop lines of data in a SQL table using Alteryx? The only solution I've come up with is to filter out data I don't want and rewrite tables... but that doesn't work well with in-DB tools if you're reading/rewriting the same table.

Any thoughts?

-Zach

[rdoptis · Accepted solution]

With the traditional input / output tools, DELETE FROM statements can be placed in the pre/post SQL boxes. This is the most convienient place I have found to execute DML statements for which Alteryx doesn't have a dedicated tool for.

Ryan

[zachwalz]

Thanks, rdoptis! Do you know if there is a way to create a variable in Alteryx to be used in that statement? E.g.

DELETE FROM tablename

WHERE id IN ([Alteryx Variable Here])

[zachwalz]

Actually, I think I figured it out:

If I create a dummy table with the rows to delete, I can then put a post-create SQL statement with the DELETE FROM with an IN condition using the dummy table data. I then drop the dummy table in the same post-creat SQL statement :).

Thanks for your advice!!!

-Zach

[rdoptis]

Cool - nice work. 

The pre/post can be very powerful. Also note the pre-SQL is executed every time the tool config is changed as Alteryx wants to have the best downstream metadata (EX: what fields are available, datatypes, etc). With this, watch out when putting certian types of DML in there such as DELETEs.

Cheers,

Ryan