Get the list of records to delete based on a column and list of tables

Question

I have a table Employees:

EmployeeId	EmployeeMatricule
1	XCV
2	HKO

I have a list of EmployeeMatricule that I need to use to delete records from the table Employees and the tables which are in relationship with it having one of their columns name EmployeeId.

To find the tables with the EmployeeId column :

 SELECT table_name
        FROM all_tab_columns
        WHERE column_name = 'EmployeeId' AND table_name <> 'Employees'

I have as an output these tables as a result of the above query

• EmployeeDepartment
• SalaryPackage
• SalaryPayroll

Let's say this is the list of EmployeeMatricule I have : XCV, HKO, MXP

So I want for each table EmployeeDepartment/SalaryPackage/SalaryPayroll :

SELECT *
FROM EmployeeDepartment
WHERE EmployeeId IN (
    SELECT DISTINCT EmployeeId
    FROM Employees
    WHERE EmployeeMatricule IN ('XCV', 'HKO', 'MXP'))

The same query for SalaryPackage and SalaryPayroll.

I tried using a cursor :

DECLARE
    CURSOR table_cursor IS
        SELECT table_name
        FROM all_tab_columns
        WHERE column_name = 'EmployeeId' AND table_name <> 'Employees'; 

    v_table_name VARCHAR2(128);
    v_sql VARCHAR2(4000);
    EmployeeId Employees.EmployeeId%TYPE;
BEGIN
    OPEN table_cursor; 

    LOOP
        FETCH table_cursor INTO v_table_name; 

        EXIT WHEN table_cursor%NOTFOUND; 

        v_sql := 'SELECT EmployeeId FROM ' || v_table_name ||
                 ' WHERE EmployeeId IN (SELECT DISTINCT EmployeeId FROM employee WHERE EmployeeMatricule IN (''XCV'', ''HKO'', ''MXP''))';

        DBMS_OUTPUT.PUT_LINE('Executing SELECT on table: ' || v_table_name);

        FOR rec IN (EXECUTE IMMEDIATE v_sql)
        LOOP
            DBMS_OUTPUT.PUT_LINE('EmployeeId: ' || rec.EmployeeId);
        END LOOP;
    END LOOP;

    CLOSE table_cursor; 
END;

My current output is :

Executing SELECT on table : EmployeeDepartment

Executing SELECT on table : SalaryPackage

Executing SELECT on table : SalaryPayroll

While I am expecting to return the rows with EmployeeId to delete.

Answer 1

Don't SELECT and then DELETE. Just DELETE the values from the tables.

BEGIN
  FOR t IN (
    SELECT table_name
    FROM   all_tab_columns
    WHERE  column_name =  'EMPLOYEEID'
    AND    table_name  <> 'EMPLOYEES'
    AND    owner       =  USER
  )
  LOOP
    EXECUTE IMMEDIATE 
      'DELETE FROM "' || t.table_name || q'[" t
      WHERE EXISTS (
        SELECT 1
        FROM   employees e
        WHERE  e.employeeid = t.employeeid
        AND    e.EmployeeMatricule IN ('XCV', 'HKO', 'MXP')
      )]';
  END LOOP;
END;
/

---

If you need to print what was deleted then use a RETURNING ... INTO ... clause.

DECLARE
  v_ids SYS.ODCINUMBERLIST;
BEGIN
  FOR t IN (
    SELECT table_name
    FROM   all_tab_columns
    WHERE  column_name =  'EMPLOYEEID'
    AND    table_name  <> 'EMPLOYEES'
    AND    owner       =  USER
  )
  LOOP
    EXECUTE IMMEDIATE 
      'DECLARE 
        v_ids SYS.ODCINUMBERLIST;
      BEGIN
        DELETE FROM "' || t.table_name || q'[" t
        WHERE EXISTS (
          SELECT 1
          FROM   employees e
          WHERE  e.employeeid = t.employeeid
          AND    e.EmployeeMatricule IN ('XCV', 'HKO', 'MXP')
        ) RETURNING employeeid BULK COLLECT INTO v_ids;
        :1 := v_ids;
      END;]'
      USING OUT v_ids;

    DBMS_OUTPUT.PUT_LINE(t.table_name);
    FOR i in 1 .. v_ids.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(v_ids(i));
    END LOOP;

  END LOOP;
END;
/

Which for the sample data:

CREATE TABLE employees (employeeid, employeematricule) AS
SELECT 1, 'XCV' FROM DUAL UNION ALL
SELECT 1, 'HKO' FROM DUAL UNION ALL
SELECT 1, 'MXP' FROM DUAL UNION ALL
SELECT 2, 'XCV' FROM DUAL UNION ALL
SELECT 3, 'AAA' FROM DUAL;

CREATE TABLE table1 (employeeid) AS
SELECT 1 FROM DUAL UNION ALL
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL UNION ALL
SELECT 4 FROM DUAL;

CREATE TABLE table2 (employeeid) AS
SELECT 2 FROM DUAL UNION ALL
SELECT 3 FROM DUAL;

Deletes the rows:

TABLE1
1
2
TABLE2
2

fiddle