parsing error on delete query after a cte

[SebastiendOrnano]

SQLITE
I am using the chart component to display the sprints of a project. Some sprints are linked to a parent sprint. When I need to update/delete a parent spint I must also update/delete the child sprints. So, I am exploring recursive cte's. It works when I update the parent sprint but when I have an error I don't understand when I try to delete a parent sprint and all his children :

sql parser error: Expected: SELECT, VALUES, or a subquery in the query body, found: DELETE

As I understand the error, it seems not authorized to make directly a delete query after a CTE ? But I don't master CTE's so I am probably wrong in my query (but in sql forums it said that it is possible)

Thanks in advance for any help !

WITH RECURSIVE cte_child(sprint_id,sprint_parent_id) AS 
(
    SELECT
        s.sprint_id,
        s.sprint_parent_id
    FROM project_sprints as s
    WHERE s.sprint_id = $sprint_id  
    
    UNION ALL  
    
    SELECT 
        s.sprint_id,
        s.sprint_parent_id
    FROM project_sprints as s
    JOIN cte_child AS c ON c.sprint_id = s.sprint_parent_id
)

DELETE FROM project_sprints WHERE sprint_id IN (SELECT sprint_id FROM cte_child);

[lovasoa]

Support for with clauses in delete statements is missing in SQLPage. But that's not what you should be using in your case anyway.

To handle hierarchical data like parent-child sprints, foreign keys with ON DELETE CASCADE provide a cleaner, more reliable solution than recursive CTEs. Here's why and how to do it:

The Problem with Manual Deletes

Your current approach requires recursively querying child sprints and deleting them manually. This is error-prone and complex.
Foreign keys automate this by letting the database handle deletions cascading through relationships.

How Foreign Keys Work

1. Define a self-referencing foreign key in your project_sprints table:

   CREATE TABLE project_sprints (
     sprint_id INTEGER PRIMARY KEY,
     sprint_parent_id INTEGER,
     -- ... other columns ...
     FOREIGN KEY (sprint_parent_id) 
       REFERENCES project_sprints(sprint_id) 
       ON DELETE CASCADE -- This is critical
   );

2. Enable foreign key support in SQLite (required for it to work):

   PRAGMA foreign_keys = ON;

What Happens When You Delete

• When you delete a parent sprint:

  DELETE FROM project_sprints WHERE sprint_id = $parent_id;

• The database automatically deletes all direct children of the parent sprint.
• Children of those children are also deleted (recursively cascading through the hierarchy).

Key Advantages

• Simplicity: No need for complex CTEs or manual recursion.
• Data Integrity: The database ensures consistency (no orphaned child sprints at any time, whatever insert, update or delete your perform).
• Atomicity: The entire operation succeeds or fails as one transaction.
• Performance: Optimized at the database level.

Important Notes

• Schema Update: You’ll need to recreate your table if the foreign key isn’t already defined.
• Circular References: Ensure your data has no loops (e.g., a sprint being its own grandparent).

By defining proper foreign keys, you delegate hierarchical operations to the database engine, which is designed to handle them efficiently and safely.

[SebastiendOrnano]

It works fine (but with DELETE FROM project_sprints WHERE sprint_id = $sprint_id; instead of $sprint_parent_id) !

Thanks a lot !!! The chart component is a great feature !