Purging data 2

[lynnyb]

I am trying to purge data from a group of tables but can only purge specific transactions based on a type. The type of transaction is only stored in one table and the rest only have the primary key in common.

Right now I am creating a jdbc program to group the transactions in an array to be purged. Next I loop through each of that tables that need to be purge and remove the transactions.

Does any one now of an easier/better way to do this?

 

[ddiamond]

I can't say whether this is an easier or better way to do this, but you could do it with a simple sql script.

First, use a bulk insert statement to load a temporary table with the primary keys you wish to purge (based on the transaction type).

Then use a bulk delete statement with an in clause or an exists clause to purge records from each table who's primary/foreign keys are in the tempory table.

 

[juliane26]

well, if using referential constraints, setting 'on delete cascade' might do the job, if it is not a problem somewhere else.
Then you just have to delete in your main table, all other will be deleted since it is cascaded over references.

be careful with such things, but it might be a solution here.

Juliane

 

[lynnyb]

Thanks for taking the time to respond. I really appreciate it.

I will definitely try the bulk load and delete and see if that helps.

I cann't use cascade on delete because there are no relationships between tables. I should have specified that earlier.