Try this

Assuming A.Key links to B.AKey, and B.Key links to C.BKey....

DELETE FROM A WHERE EXISTS
(SELECT * FROM C WHERE C.F1 = ACertainValue
AND C.BKey IN (SELECT KEY FROM B WHERE B.AKey = A.KEY));

or alternatively

DELETE FROM A WHERE EXISTS
(SELECT * FROM C WHERE C.F1 = ACertainValue
AND EXISTS (SELECT * FROM B WHERE B.CKey = C.Key
AND B.AKey = A.KEY));


Cascading deletes from A through B and C should be defined in the FKs
or in the relevant triggers.

Roger Vellacott


--- In ib-support@y..., Svein Erling Tysvær
<svein.erling.tysvaer@k...> wrote:

> >I have 3 tables, A is the parent of B which is the parent of C. I
> >want to delete all the records related to a record in A if one of

the

> >fields in C is a certain value
>
> CREATE PROCEDURE DELETE_ALL_A(c_value Integer)
> AS
> DECLARE VARIABLE A_PK Integer;
> DECLARE VARIABLE B_PK Integer;
> BEGIN
> FOR SELECT DISTINCT A.PK FROM A
> JOIN B ON B.FK = A.PK
> JOIN C ON C.FK = B.PK
> WHERE C.FIELD = :c_value INTO :A_PK DO BEGIN
> FOR SELECT DISTINCT B.PK FROM B
> WHERE B.FK = A_PK INTO :B_PK DO BEGIN
> DELETE FROM C
> WHERE FK = B_PK;
> END;
> DELETE FROM B
> WHERE FK = A_PK
> DELETE FROM A
> WHERE PK = A_PK
> END;
> END;
>
> Please bear in mind that I generally do not write SPs and expect

there to

> be a few syntax errors.
>
> HTH,
> Set