Delete database records

[nbt1032]

I am working through a data cleansing project to cleanup a master data table.  I have identified a set of records that I would like to delete based on the below workflow.   This is a test workflow, and the final logic will become more complex, but there will always be an output dataset to be deleted.

I would like to delete the all the records (397,400) specified in the duplicate branch.  Can anyone suggest how this could be done?

Thx.

[RodL · Accepted solution]

A couple of ideas to go about it (there's probably others)...

• You could just overwrite the existing table with the records you want to keep (i.e., the Unique side),
• or you are going to need something that basically does a "delete from table where..." query. This will require writing to a temp/stage table as an output and then put the delete statement in the Post SQL process to connect the master table to the temp/stage table. (You will need to include not only the primary keys in the WHERE side, but at least one of the other fields that would be unique to those specific records.) You would end the Post SQL with a DROP statement for the temp table.

[nbt1032]

I like this.  I was hoping there would be a bit more of a direct way to do this, but this is certainly a workable answer.  Thx.

[Nikkath_15]

Am trying to delete the Oracle DB records using Dynamic Input query

i have given "select * from table name where COLUMN_NAME in('X')" in TABLE/Query

and in post SQL statement "Delete from table name where COLUMN_NAME in('X')"

i have used 'X' as update where clause 

But getting error as "ORA-01722:invalid number"

Can anybody have solution ?