Recursive delete of multiple rows in one table

user_12887620 0
Viewed 342

I have a problem with my postgres database. I have a table Tasks with 3 columns: ID, Name and Parent_ID (which refers to another task id in this table):

id | name | parent_id 
---+------+-----------
 1 | A    |     0
 2 | B    |     1
 3 | C    |     2
 4 | D    |     1
 5 | E    |     0
 6 | F    |     0

So basically it's like this: 

1. A
2.     B
3.          C
4.     D
5. E
6. F

What I'm trying to do is to delete task A, and delete all of its children and all children of children etc etc..(in this case B and D, along with C as its children of B which is deleted) something like cascade delete, but i cant do this. Maybe any function will work?

The result after delete should be 

id | name | parent_id
---+------+-----------
 5 | E    |     0
 6 | F    |     0

Hope you guys can help me.

recursive-query sql-delete postgresql sql database
3 Answers

You only need a cascading FK-constraint:

\i tmp.sql

CREATE TABLE employees (
   id serial PRIMARY KEY
   , name VARCHAR NOT NULL
   , parent_id INT REFERENCES employees(id) ON DELETE CASCADE
);

INSERT INTO employees
VALUES (1,'A', NULL),(2,'B', 1),(3,'C', 2),
       (4,'D', 1),(5,'E', NULL),(6,'F', NULL);

DELETE FROM employees
WHERE name = 'A'
        ;

SELECT * FROM employees
        ;


----------

Result:

----------

    DROP SCHEMA
    CREATE SCHEMA
    SET
    CREATE TABLE
    INSERT 0 6
    DELETE 1
     id | name | parent_id 
    ----+------+-----------
      5 | E    |          
      6 | F    |          
    (2 rows)
user_9059020

You can build the list of all descendent rows from the starting name with a recursive query, and then use it as a filter for the deletetions.

with recursive cte(id, parent_id) as (
    select id, parent_id from mytable where name = 'A'
    union all
    select t.id, t.parent_id from mytable t inner join cte c on c.id = t.parent_id
)
delete from mytable where id in (select id from cte)

Demo on DB Fiddle - table content after executing the query:

id | name | parent_id
-: | :--- | --------:
 5 | E    |         0
 6 | F    |         0
user_106767160

Use recursive cte to get all rows with Name = 'A' and it's subordinates. Then delete it from table employees.

Here is the step to create the table:

Sample table: CREATE TABLE employees (
   id serial PRIMARY KEY,
   name VARCHAR NOT NULL,
   parent_id INT
);

INSERT INTO employees 
VALUES (1,'A', 0),(2,'B', 1),(3,'C', 2),
       (4,'D', 1),(5,'E', 0),(6,'F', 0);

Query: 

WITH RECURSIVE subordinates AS (
   SELECT
      id,
      parent_id,
      name
   FROM
      employees
   WHERE
      name = 'A'
   UNION
      SELECT
         e.id,
         e.parent_id,
         e.name
      FROM
         employees e
      INNER JOIN subordinates s ON s.id = e.parent_id
) 
DELETE 
FROM employees
WHERE id in (
  SELECT
    id
  FROM
   subordinates);

SELECT * FROM employees;
user_73718930
Related