Deleting Rows from Parent and Child Tables

Deleting rows from parent and child tables

Two possible approaches.

1. If you have a foreign key, declare it as on-delete-cascade and delete the parent rows older than 30 days. All the child rows will be deleted automatically.

2. Based on your description, it looks like you know the parent rows that you want to delete and need to delete the corresponding child rows. Have you tried SQL like this?

     delete from child_table
         where parent_id in (
              select parent_id from parent_table
                   where updd_tms != (sysdate-30)
   

   -- now delete the parent table records

   delete from parent_table
   where updd_tms != (sysdate-30);
   

---- Based on your requirement, it looks like you might have to use PL/SQL. I'll see if someone can post a pure SQL solution to this (in which case that would definitely be the way to go).

declare
    v_sqlcode number;
    PRAGMA EXCEPTION_INIT(foreign_key_violated, -02291);
begin
    for v_rec in (select parent_id, child id from child_table
                         where updd_tms != (sysdate-30) ) loop

    -- delete the children
    delete from child_table where child_id = v_rec.child_id;

    -- delete the parent. If we get foreign key violation, 
    -- stop this step and continue the loop
    begin
       delete from parent_table
          where parent_id = v_rec.parent_id;
    exception
       when foreign_key_violated
         then null;
    end;
 end loop;
end;
/

Delete rows from parent table after deleting multiple children SQL Server

so, I set up two tables... ParentTable with a key of ParentID, 
and ChildTable, key of ChildID and FK of ParentID.


delete dbo.parenttable
where parentid = 3
-- produces error because rows exist in dbo.childtable where parentid = 3

delete dbo.childtable 
where parentid = 3
-- deletes all rows in dbo.childtable where parentid = 3

-- Assuming This is where you are now
--- .... needing to find all rows in parent table 
---      where there are no corresponding child 
---      rows in dbo.childtable


with CTE_Parent as          --wrap up the selected ID's in a CTE expression
(select dbo.parenttable.parentid
from dbo.parenttable
left outer join dbo.childtable 
on dbo.parenttable.parentid = dbo.childtable.parentid   
where dbo.childtable.parentid is null  --- trick to find non-existent child recs
)

delete dbo.parenttable
from dbo.parenttable
inner join cte_Parent
on dbo.parenttable.parentid = cte_parent.parentid

Dynamically delete rows from parent table after deleting rows from multiple child table

Create DELETE statements using recursive CTE. Statements must be run in the do desc order. Replace '=1' with a proper value.

with cte as (
    select cast('delete' + x.childAlias + ' from ' + Child_table + x.childAlias +
           case when Parent_Column is null then ' where ' + Child_column + ' = 1;' else '' end as nvarchar(max))  sql,
        x.childAlias, Child_column, 
        Parent_Table, Parent_Column, 
        Delete_Order do
    from tbl
    cross apply (select ' t' + cast(tbl.Delete_Order as varchar(3))) x(childAlias)
    
    union all
    
    select cte.sql + ' join ' + tbl.Child_table + x.childAlias + 
           ' on' + cte.childAlias+ '.' + cte.Child_column + ' =' + x.childAlias + '.'+ cte.Parent_Column +
           case when tbl.Parent_Column is null then ' where' + x.childAlias + '.' + tbl.Child_column + ' = 1;' else '' end  sql,
        x.childAlias, tbl.Child_column,
        tbl.Parent_Table, tbl.Parent_Column,
        do  
    from cte
    join tbl on cte.Parent_Table = tbl.Child_table
    cross apply (select ' t' + cast(tbl.Delete_Order as varchar(3))) x(childAlias)
 ) 
 select sql, do
 from cte
 where sql like '%where%'
 order by do desc

db<>fiddle

Oracle - delete all child records for a parent

This is pretty much what primary keys and foreign keys and clauses like ON DELETE CASCADE are for. If it's not too late, you can try adding PK and FK constraints before you do any deletions; then everything will be easy.

ADDED: Based on further discussion. The query below can be used to find all descendant tables of a parent table. The query may probably be improved in many ways, but it may be an OK starting point.

with f as (
        select constraint_name, table_name, r_constraint_name
        from   user_constraints
        where  constraint_type = 'R'
     ),
     p as (
        select constraint_name, table_name
        from   user_constraints
        where  constraint_type = 'P'
     ),
     j (child_table, f_key, parent_table, p_key) as (
        select f.table_name, f.constraint_name, p.table_name, f.r_constraint_name
        from   p join f on p.constraint_name = f.r_constraint_name
        union all
        select 'EMPLOYEES', (select constraint_name from p 
                                where table_name = 'EMPLOYEES'), null, null from dual
     )
select level as lvl, j.*
from j
start with parent_table is null
connect by nocycle parent_table = prior child_table
order by lvl, parent_table, child_table;

The "parent" table in this case is EMPLOYEES and the name appears twice, on the same line. That can be made into a bind variable if needed. I used EMPLOYEES (note: it must be in all-caps because that's how string values are stored in system tables) because I ran this on the standard HR schema; output:

  LVL CHILD_TABLE       F_KEY                PARENT_TABLE      P_KEY
----- ----------------- -------------------- ----------------- -----------------
    1 EMPLOYEES         EMP_EMP_ID_PK
    2 DEPARTMENTS       DEPT_MGR_FK          EMPLOYEES         EMP_EMP_ID_PK
    2 JOB_HISTORY       JHIST_EMP_FK         EMPLOYEES         EMP_EMP_ID_PK
    3 JOB_HISTORY       JHIST_DEPT_FK        DEPARTMENTS       DEPT_ID_PK

Deleting rows in parent table when there is no parent_id association in child tables

//One way of doing is getscalar cols from the child tables and exclude from 
  the parent

delete from Location 
where ID_Location  not in
(
Select ID_Location from Customer
union
Select ID_Location from Shop
)

how delete parent row with all child row from other table

Use foreign keys:

CREATE TABLE parent (
  id INT NOT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (
  id INT,
  parent_id INT,
  INDEX par_ind (parent_id),
  FOREIGN KEY (parent_id)
  REFERENCES parent(id)
  ON DELETE CASCADE
) ENGINE=INNODB;


INSERT INTO parent VALUES (1), (2);
INSERT INTO child VALUES (1, 1);

# This query implicitly removes from `child` where parent_id = 1
DELETE FROM parent WHERE id = 1;

---

Related Topics