Delete records using Script, variables and multiple values

[Former Member]

Hi

I have a source file in one DB and target file in another DB.

I want to delete records in the target file where targetfile.month=sourcefile.month

How can I achieve this?

I tried to use scripts with variables

Example :

$variable1 = sql('DB1', 'select month from sourcefile');

sql('DB2', ' delete from target file where month = {$variable1} ')

But apparently since $variable1 can hold only one value I am not getting the results I want. I have multiple values for the first statement above and its not fixed. How do I store multiple values in a variable or create an array? please eplain with the format for any function if we have one.

Cheers.

Using DS Designer 14.2.1.568

[former_member187605]

You can implement a delete in a data flow, too.

• Start with source table.
• In Query transform: select month from source.
• Then add a Map_Operation transform to change row types from Normal to Delete.
• Connect to target table.

[former_member187605]

If you don't trust me, check How to delete records? - Enterprise Information Management - SCN Wiki for a detailed explanation with screenshots et al.

[Former Member]

The solution provided by dirk will work. I'm pretty sure!

[Former Member]

Are the DBs on same server? Use a post load command on the target table.

Some thing like this -

Delete from table1 inner join table2 on table1.month = table2.month