Loading ...
Sorry, an error occurred while loading the content.

Re: [ib-support] Deleting from linked tables

Expand Messages
  • Svein Erling Tysvær
    ... 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
    Message 1 of 4 , Oct 1, 2001
    • 0 Attachment
      >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
    • Roger Vellacott
      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
      Message 2 of 4 , Oct 1, 2001
      • 0 Attachment
        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));

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

        Roger Vellacott

        Robert Munro 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. >

        <It doesn't seem like an unlikely scenario. How can I do this? >
      • rvellacott@passfield.co.uk
        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
        Message 3 of 4 , Oct 3, 2001
        • 0 Attachment
          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
        Your message has been successfully submitted and would be delivered to recipients shortly.