4 Replies Latest reply on Nov 17, 2009 2:58 PM by 711834

    Delete rows from multiple tables

    711834
      Hi There,

      I have a schema of around 700 tables within our Oracle db. There is a group of about 20 tables that i need to delete a provider from.

      A provider is added to the provider_tbl, and issuesd a prrovider_id (PK), they can then also be added to multiple other tables, once say a contact ddress is entered (address_tbl).

      I have the PK/FK relationships, my issues is how to structure a script to look through each of the cascading tables...

      Apologies, but i am just staring out with PL/SQL. I have eperience with VBA, using that i would do a COUNT and use a FOR NEXT LOOP to take the cursor through and delete....

      Thanks in advance,

      Patrick
        • 1. Re: Delete rows from multiple tables
          asifkabirdba
          DECLARE

          count NUMBER := 0;
          total NUMBER := 0;

          CURSOR del_record_cur IS
          SELECT rowid
          FROM .
          WHERE
          BEGIN
          FOR rec IN del_record_cur LOOP
          DELETE FROM .
          WHERE rowid = rec.rowid;
          total := total + 1;
          count := count + 1;
          IF (count >= 1000) THEN
          COMMIT;
          count := 0;
          END IF;
          END LOOP;
          COMMIT;
          DBMS_OUTPUT.PUT_LINE('Deleted ' total ' records from ..');
          END;
          /


          Regards
          Asif Kabir
          • 2. Re: Delete rows from multiple tables
            711834
            Asif - thanks v much for your speedy reply.

            I was wondering whether you would mind just briefly running through that script for me...

            particulary the IN cursor LOOP part. My understanding is that the curser will pick up the select row e.g.

            SELECT *
            FROM provider_tbl
            WHERE provider_id = '100'

            as part of the script body, i am unsure where to reference the other tables this record may have been included as an PK constraint?

            sorry
            • 3. Re: Delete rows from multiple tables
              John Spencer
              Assuming that the other 19 tables that have provider_id all reference provider_tbl as an FK, then it is relatively simple.

              As the owenr of these table you would use the user_constraints table to determine the tables that have an FK relationship with provider_tbl.

              Something like:
              SELECT table_name
              FROM user_constraints
              WHERE r_constraint_name = (SELECT constraint_name
                                         FROM user_constraints
                                         WHERE table_name = 'PROVIDER_TBL' and
                                               constraint_type = 'P')
              Assuming that you know the provider_id of the provider you want to delete, then you could do something like:
              DECLARE
                 l_provider NUMBER := 1234;
              BEGIN
                 FOR rec IN (SELECT table_name
                             FROM user_constraints
                             WHERE r_constraint_name = (SELECT constraint_name
                                                        FROM user_constraints
                                                        WHERE table_name = 'PROVIDER_TBL' and
                                                              constraint_type = 'P')) LOOP
                    EXECUTE IMMEDIATE 'DELETE FROM '||rec.table_name||' WHERE PROVIDER_id = :b1' USING l_prpovider;
                 END LOOP;
                 COMMIT;
              END;
              John
              1 person found this helpful
              • 4. Re: Delete rows from multiple tables
                711834
                John,

                Many thanks for this. i am working through, and will post the result.

                cheers

                Patrick