Delete certain rows in Oracle table (ODBC) before inserting/appending new rows

[Mchappell67]

Hi, all -

I've searched quite a bit and tried a few things, to no avail.

I need to insert rows into an Oracle table, but I want to remove any existing rows for the same (key1+key2).  I'm attempting to use the "Dynamic Input" tool, as discussed in this thread:  Solved: Delete Specific records from a SQL table with a cl... - Alteryx Community.  I'm experiencing the same problem as the last post in the thread:  errors out with "No columns returned".  The value substitution seems to be working, i.e. in the error message I can see the SQL statement with the proper key values.  I've tried a few variations on adding a "SELECT" statement prior to the "DELETE" statement, all with the same results.  If I don't add a "SELECT" statement, it seems like Alteryx is adding one, i.e. "SELECT * from (Delete from table where key='key1')" (or something similar).

I can add a trigger on the table to accomplish this, but I'm trying to avoid creating packages/procedures/functions in the database; I'd prefer the workflow to be self-contained, if at all possible.

Any insights/hints would be appreciated.

Thanks,

Mark

[geraldo]

@Mchappell67 

One way to do this deletion procedure is in the pre-sql option. As you need to change the parameter you must necessarily put it in a batch macro and replace the parameters in the where clause

an example of pre sql with deletion
the XXXX will be changed in the macro

[Mchappell67 · Accepted solution]

Hi -

Thanks - these seems like a relatively pain-free solution!  I'll give this a try.

Mark