Delete database records

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.

Datasets

Output

Accepted answers

RodL

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.

All comments

RodL

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.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703