Deleting Hierarchical Data in SQL Table

Deleting hierarchical data in SQL table

When the number of rows is not too large, erikkallen's recursive approach works.

Here's an alternative that uses a temporary table to collect all children:

create table #nodes (id int primary key)
insert into #nodes (id) values (@delete_id)
while @@rowcount > 0
    insert into #nodes 
    select distinct child.id 
    from table child
    inner join #nodes parent on child.parentid = parent.id
    where child.id not in (select id from #nodes)

delete
from table
where id in (select id from #nodes)

It starts with the row with @delete_id and descends from there. The where statement is to protect from recursion; if you are sure there is none, you can leave it out.

delete/select hierarchy data in postgresql

Keep it simple, place the parameter in the initial query of recursive with:

with recursive cbase as (
    select 1 as id -- select $1 as id

    union all

    select child.id
    from comment as child
    join cbase on cbase.id = child.parent_comment_id
)
delete from comment 
where id in (select * from cbase)
returning id;

 id 
----
  1
  2
  3
(3 rows)

DELETE 3    

The best way to delete items in hierarchy way from a DB table in mysql?

The easiest way seems to be this:

1. Create a foreign key constraint between the parent_category and category_id column;

2. Set cascade delete on.

   create table yourTable
   ( ...
     add constraint
     foreign key (parent_category)
     references yourTable(category_id)
     on delete cascade
   )
   

This will solve your problem totally without effort.

Delete all level child item using sql query

How about this query:

DECLARE @DelID INT
SET @DelID=1

;WITH T(xParent, xChild)AS
(
SELECT ParentID, ChildId FROM Table WHERE ParentID=@DelID
UNION ALL
SELECT ParentID, ChildId FROM TABLE INNER JOIN T ON ParentID=xChild
)
DELETE FROM TABLE WHERE ParentID IN (SELECT xParent FROM T)

Deleting records from a table with heirachial data

In this case you can take advantage of DELETE CASCADE feature of SQL Server.

If you have parent-child mapping between two tables in SQL you can delete child table records along with a single query to delete parent records.

To achieve this you will have to enable Delete cascading while establishing the relationship between the tables, following way:

CREATE TABLE products
( product_id INT PRIMARY KEY,
  product_name VARCHAR(50) NOT NULL,
  category VARCHAR(25)
);

CREATE TABLE inventory
( inventory_id INT PRIMARY KEY,
  product_id INT NOT NULL,
  quantity INT,
  min_level INT,
  max_level INT,
  CONSTRAINT fk_inv_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (product_id)
    ON DELETE CASCADE
);

Or if you have already defined the table data you can use the following method to do this:

ALTER TABLE dbo.T2
   DROP CONSTRAINT FK_T1_T2   -- or whatever it's called

ALTER TABLE dbo.T2
   ADD CONSTRAINT FK_T1_T2_Cascade
   FOREIGN KEY (EmployeeID) REFERENCES dbo.T1(EmployeeID) ON DELETE CASCADE

or If you are using MYSQL (as it is not mentioned in question) you can follow the instructions on following StackOverflow post:

MySQL foreign key constraints, cascade delete

---

Related Topics