Full SQL Pushdown for Delete on joined tables

[Former Member]

Hi,

I'm trying to figure out how to have Data Services generate one sql statement that deletes records from a target table joined with a temporary staging table based upon id. I cant figure out how to do this? Right now i'm mapping all normal rows to deletes, but that is still trying to issue multiple deletes from in-memory. Here is my dataflow:

I really do not want to issue a manual SQL statement to accomplish this. Can anybody help? This optimized sql should look something like this:

DELETE FROM consumers USING oxxford_suppress WHERE oxxford_suppress.id = consumers.id;

[Former Member]

Two things you can do -

Write the DELETE stmt in a script and attach after the data flow, like this

sql('datastorename','DELETE FROM consumers USING oxxford_suppress WHERE oxxford_suppress.id = consumers.id');

OR

Insert the delete statement as a post load command in the target table. I would prefer the first one as it is easy to manage.

[Former Member]

I was hoping that wasn't the answer haha.

Thanks you,