Subject Re: [ib-support] Mass deletion
Author Nataraj S Narayanan
On Mon, 8 Apr 2002, Helen Borrie wrote:


The Tables to delete data are Drug_Bill and Drug_Bill_Item ,where the 2nd
table has a foriegn key of Drug_Bill by name Bill_Id.

As said by Helen I gave the command 'Delete from Drug_Bill_Item where
Bill_id in (select Bill_id from Drug_bill Where Bill_Date between
'01-JAN-1999' and '31-DEC-1999')

Unfortunately, the operation takes aeons (7 hours) to complete. The gdb is
Mb. These 2 tables are the most populous. I used IB_SQL which seems to be
developed using IBO.
Frequently the IB_SQL on NT Workstation stops responding after
sometime. After resetting i got a deadlock. Sometimes unable to access the

Pl help me out.

> At 01:03 AM 08-04-02 +0530, you wrote:
> >Hi
> >
> >Is there a way to delete at one stretch the records in a gdb pertaining to
> >say date '01-APR-2001'? Now i have to remove the constraints like foriegn
> >key for each table and then delete using Sql statement.
> >
> >Then I datapump to the an empty gdb. Surely there ought to be an easier
> >way?
> For IB 4.x, IB 5.x, Dialect 1 IB 6.x or Firebird data:
> WHERE Mydate between '01-APR-2001' and '02-APR-2001' ;
> If there are rows in another table that are dependent on these rows through
> a foreign key, you will have to delete them first:
> WHERE For_Key in (SELECT Pri_Key from ATABLE
> WHERE Mydate between '01-APR-2001' and '02-APR-2001') ;
> ...and commit this...
> cheers,
> Helen

Nataraj S Narayanan
Synergy Info Systems
Kochi,India-683 503
Ph:95 484 557244