CODEWITHSUNDEEP
sqlrecursion
JaySym
JaySym

Reputation: 63

How to use recursive logic to update SQL table

I have a table with the following data:

Id   ParentId   IsTrue  IsBranchHead   BranchId
------------------------------------------------
A     Null        0          0          Null
B      A          1          1          Null
C     Null        0          0          Null
D      B          1          0          Null
E      D          1          0          Null
F      A          0          1          Null
G      C          0          1          Null
H      B          1          0          Null
I      E          1          0          Null
J     Null        0          0          Null
K      J          1          1          Null
L      J          1          1          Null
M      L          1          0          Null

Structurally looks like this, with the IsTrue value in (), second teir will always have IsBranchHead = 1: 

A(0)
     F(0)
     B(1)
          H(1)
          D(1)
               E(1)
                    I(1)
C(0)
     G(0)
J(0)
     K(1)
     L(1)
          M(1)

The end result table need to update the BranchId field to the Id of the BranchHead when IsTrue = 1 to the following result

Id   ParentId   IsTrue  IsBranchHead   BranchId
------------------------------------------------
A     Null        0          0          Null
B      A          1          1          B
C     Null        0          0          Null
D      B          1          0          B
E      D          1          0          B
F      A          0          1          Null
G      C          0          1          Null
H      B          1          0          B
I      E          1          0          B
J     Null        0          0          Null
K      J          1          1          K
L      J          1          1          L
M      L          1          0          L

Now this can be done in code pretty easy with loops and some conditions, but I have no idea how to do this in SQL. Any help would be greatly appreciated!

Upvotes: 0

Views: 37

Answers (1)

Eric
Eric

Reputation: 5743

This is one of the solution for SQL server

fiddle

-- Data
DECLARE @data TABLE ([Id] varchar(1), [ParentId] varchar(1), [IsTrue] int, [IsBranchHead] int, [BranchId] varchar(1));

INSERT INTO @data
VALUES
    ('A', NULL, 0, 0, NULL),
    ('B', 'A', 1, 1, NULL),
    ('C', NULL, 0, 0, NULL),
    ('D', 'B', 1, 0, NULL),
    ('E', 'D', 1, 0, NULL),
    ('F', 'A', 0, 1, NULL),
    ('G', 'C', 0, 1, NULL),
    ('H', 'B', 1, 0, NULL),
    ('I', 'E', 1, 0, NULL),
    ('J', NULL, 0, 0, NULL),
    ('K', 'J', 1, 1, NULL),
    ('L', 'J', 1, 1, NULL),
    ('M', 'L', 1, 0, NULL)
;

-- Recursive CTE
WITH cte AS
(
    SELECT 
        Id, ParentId, IsTrue, IsBranchHead, 
        CASE WHEN IsTrue = 1 AND IsBranchHead = 1 THEN Id END AS BranchId 
    FROM @data WHERE ParentId IS NULL
    UNION ALL
    SELECT 
        d.Id, d.ParentId, d.IsTrue, d.IsBranchHead, 
        CASE 
            WHEN d.IsTrue = 1 AND d.IsBranchHead = 1 THEN d.Id -- Use Id as BranchId
            WHEN d.IsTrue = 1 THEN cte.BranchId -- Copy parent BranchId
            ELSE d.BranchId -- Update nothing
        END
    FROM cte INNER JOIN @data d ON cte.Id = d.ParentId
)
UPDATE target SET BranchId = cte.BranchId FROM @data target INNER JOIN cte 
ON target.Id = cte.Id 
--SELECT * FROM cte
--ORDER BY Id

SELECT * FROM @data

Upvotes: 1

Related Questions