Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-18511

Recursive CTE support for UPDATE (and DELETE) statements

    XMLWordPrintable

    Details

      Description

           CREATE TABLE tree (
             `Node` VARCHAR(3),
             `ParentNode` VARCHAR(3),
             `EmployeeID` INTEGER,
             `Depth` INTEGER,
             `Lineage` VARCHAR(16)
           );
       
           INSERT INTO tree
             (`Node`, `ParentNode`, `EmployeeID`, `Depth`, `Lineage`)
           VALUES
             ('100', NULL, '1001', 0, '/'),
             ('101', '100', '1002', NULL, NULL),
             ('102', '101', '1003', NULL, NULL),
             ('103', '102', '1004', NULL, NULL),
             ('104', '102', '1005', NULL, NULL),
             ('105', '102', '1006', NULL, NULL);
       
           WITH RECURSIVE prev AS (
           SELECT * FROM tree WHERE ParentNode IS NULL
           UNION
           SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
           FROM tree t JOIN prev p ON t.ParentNode = p.Node
           )
           SELECT * FROM prev;
           WITH RECURSIVE prev AS (
           SELECT * FROM tree WHERE ParentNode IS NULL
           UNION
           SELECT t.Node,t.ParentNode,t.EmployeeID,p.Depth + 1 as Depth, CONCAT(p.Lineage, t.ParentNode, '/')
           FROM prev p JOIN tree t ON t.ParentNode = p.Node
           )
           UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.Node;
       
      You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'UPDATE tree t, prev p SET t.Depth=p.Depth, t.Lineage=p.Lineage WHERE t.Node=p.No' at line 7
      

      db<>fiddle here*

      supported in MySQL-8.0 and MSSQL

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              danblack Daniel Black
              Votes:
              3 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated: