Oracle delete rows in multiple tables based on single primary key.

Question

There is about 20 tables which branch based on a single primary key - EmployeeId. There's about 12,000 employees I want completely gone from my database. The chances of other processes updating these employees when I am deleting them are close to zero. I am planning on deleting them in bulk and then committing. All the delete shouldn't ideally fail but I am unsure whether to go the cursor route, commit every 500 rows or something. Here's how it looks like now.

--STEP 1: Collect the EmployeeIds to delete in a temp table
Create table temp as select EmployeeId from Employee where <all conditions are met>;

--STEP 2: Delete names
Delete from EmployeeName where EmployeeId in (select EmployeeId from temp);

--STEP 3 - STEP 30: Delete all other child tables
Delete from table inner join another_table on some_key inner join yet_another_table on some_key where EmployeeId in (select EmployeeId from temp);

--STEP 4: Commit
commit;

You need to delete first from child then from parent (unless you are using deferred FK constraints). — Lukasz Szozda
– Lukasz Szozda, Commented Nov 10, 2017 at 15:45
Yes, true. The last step there was to delete from Employee where EmployeeId in (select EmployeeId from temp); — user3726933
– user3726933, Commented Nov 10, 2017 at 15:50

lbndev · Accepted Answer · 2017-11-10 15:52:11Z

1

If you're going to do this often, how about letting Oracle do the job for you ?

Set all your foreign keys referencing table Employee to "ON DELETE CASCADE" (see this link for example)
delete from Employee where <all your conditions>;

The FKs being set to "ON DELETE CASCADE", Oracle will automatically delete orphaned rows from child tables when a row is deleted in the parent table.

answered Nov 10, 2017 at 15:52

lbndev

8117 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

lbndev Over a year ago

This is what ON DELETE CASCADE is meant for. Whether or not it is dangerous depends on the usage pattern. With great power comes great responsibility.

Lukasz Szozda Over a year ago

Sorry, but I saw this pattern in real life where some silly CASCADE deletes too many rows (however original query looks very simple).

Mark J. Bobak Over a year ago

Just like anything else....understand the functionality and purpose. Lots of things are dangerous if applied incorrectly....

Lukasz Szozda Over a year ago

@MarkJ.Bobak Whatsoever (it was't my case :)). Better safe than sorry.

MT0 · Accepted Answer · 2017-11-10 15:55:44Z

Assuming you want to maintain the integrity of the data and when there is an error deleting from one table then you want to ROLLBACK all the deletes for that employee then you could do something like:

DECLARE
  TYPE Emp_ID_Tab_Type IS TABLE OF Employee.EmployeeID%TYPE;

  All_Employees     Emp_ID_Tab_Type;
  Deleted_Employees Emp_ID_Tab_Type := Emp_ID_Tab_Type();
  Error_Employees   Emp_ID_Tab_Type := Emp_ID_Tab_Type();
BEGIN
  SELECT EmployeeID
  BULK COLLECT INTO All_Employees
  FROM   Employees
  WHERE  1 = 0;    -- Your conditions

  FOR i IN 1 .. All_Employees.COUNT LOOP
    BEGIN
      DELETE FROM child_table1
      WHERE  EmployeeID = All_Employees(i);

      DELETE FROM child_table2
      WHERE  EmployeeID = All_Employees(i);

      -- ...

      DELETE FROM child_table20
      WHERE  EmployeeID = All_Employees(i);

      DELETE FROM Employees
      WHERE  EmployeeID = All_Employees(i);

      COMMIT;
      Deleted_Employees.EXTEND;
      Deleted_Employees(Deleted_Employees.COUNT) := All_Employees(i);
      DBMS_OUTPUT.PUT_LINE( All_Employees(i) || ' deleted' );
    EXCEPTION
      WHEN others THEN
        ROLLBACK;
        Error_Employees.EXTEND;
        Error_Employees(Error_Employees.COUNT) := All_Employees(i);
        DBMS_OUTPUT.PUT_LINE( All_Employees(i) || ' error - ' || SQLERRM );
    END;
  END LOOP;

  -- Do something with the errors
END;

It is not going to be the fastest with a COMMIT at the end of each loop but it does ensure you can ROLLBACK each employee.

Lukasz Szozda · Accepted Answer · 2017-11-10 15:59:30Z

If you are aware of any trouble during deletion and still want to do entire operation without cursor you could use DML Error Logging:

In some situations the most obvious solution to a problem is a DML statement (INSERT ... SELECT, UPDATE, DELETE), but you may choose to avoid DML because of the way it reacts to exceptions.

By default, when a DML statement fails the whole statement is rolled back, regardless of how many rows were processed successfully before the error was detected.

In the past, the only way around this problem was to process each row individually, preferably with a bulk operation using FORALL and the SAVE EXCEPTIONS clause. In Oracle 10g Database Release 2, the DML error logging feature has been introduced to solve this problem. Adding the appropriate LOG ERRORS clause on to most INSERT, UPDATE, MERGE and DELETE statements enables the operations to complete, regardless of errors.

BEGIN
  DBMS_ERRLOG.create_error_log (dml_table_name => 'EmployeeName');
END;
/

Delete from EmployeeName 
where EmployeeId in (select EmployeeId from temp)
LOG ERRORS INTO err$_EmployeeName ('DELETE') REJECT LIMIT UNLIMITED;

Collectives™ on Stack Overflow

Oracle delete rows in multiple tables based on single primary key.

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related