Dropping lines of data in a SQL table?

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

Preparation

Optimization

Database Connection

Accepted answers

rdoptis

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

All comments

rdoptis

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

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

RithiS 280