Re: How would you do a delete on this?

I hope this will work:

1. Disable foreign key constraint Cat_to_Esc ->Escalation
2. delete from Escalation where Esc_Id in (select c.esc_id from Cat_to_Esc c, Category t where t.cat_id=c.cat_id and t.comp_Id = '12345')
3. delete from cat_to_esc where cat_id in (select cat_id from category where comp_id = 12345)
4. Enable foreign key constraint Cat_to_Esc ->Escalation

Goran

Sean Dolan <nospam_at_nowhere.com> wrote in message news:971ll8017nr_at_enews3.newsguy.com...
> Enviro : Oracle 8.1.7 on NT 2000.
>
> Three tables.
>
> Category Escalation
> --------------- -----------------
> Cat_Id (PK) Esc_Id (PK)
> Comp_Id (FK) Descr
> Descr
>
>
> Cat_to_Esc
> ------------------
> Cat_Id (FK)
> Esc_Id (FK)
>
>
> I would like to delete all evidence of an Escalation where Cat_Id belongs
 to
> Comp_Id = 12345
> How?
>
> If I do a delete on the Cat_to_Esc first using :
> delete from cat_to_esc where cat_id in (select cat_id from category
 where
> comp_id = 12345)
>
> then how would I then get the Escalation Ids in the Escalation Table? And
> no, I can not do a :
> delete from escalation where escalation_id not in (select esc_id from
> cat_to_esc)
>
> because there can be escalation Ids that exist but are not part of a
> category.
>
> I am sure this is a "common" SQL question, but I have tried each and every
> way and can not come up with a quick way. I know I could use a PL/SQL
> package and fetch through a cursor deleting that way.... is that the only
> way?
>
>
> Thanks,
> Sean
>
>
>
>
Received on Thu Feb 22 2001 - 05:26:36 CST