Update Parent Status depending on Childs Status

Question

I have a hierarchical structure of my data.
Each record has a status column. If this is set, I want to inherit this upwards.

I tried using CTE to recursively determine the smallest status of the respective childs. If not all children have status 1, the parent should have status 0. If all children have status 1, the parent should also have status 1.

Here is my test data + the SQL.

DECLARE @data TABLE (
    RowNo INT NOT NULL IDENTITY (1, 1)
  , ID NVARCHAR(15)
  , [ID_Parent] NVARCHAR(15)
  , [Status] INT
);

INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1', NULL, 0);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.1', '1', 0);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.1.1', '1.1', 1);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.1.2', '1.1', 1);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.2', '1', 1);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.3', '1', 1);

SELECT *
  FROM @data

;
WITH C
AS
(SELECT T.RowNo
      , T.ID
      , T.[Status]
      , T.ID AS RootID
      FROM @data T
    UNION ALL
    SELECT T.RowNo
         , T.ID
         , T.[Status]
         , C.RootID
      FROM @data T
           INNER JOIN C
                        ON T.ID_Parent = C.ID)
SELECT T.RowNo
     , T.ID
     , T.ID_Parent
     , T.[Status]
     , S.[MINeveryNode]
  FROM @data T
       INNER JOIN (SELECT RootID
                        , MIN([Status]) AS [MINeveryNode]
             FROM C
            GROUP BY RootID) AS S
                    ON T.Id = S.RootID
 ORDER BY T.RowNo
OPTION (MAXRECURSION 0)

So what is the problem with your existing solution - what's the question? — Stu, Aug 28 '23 at 13:31
The status of Record 2 (1.1) is not determined correctly. All children have status 1, but my sql determines status 0 and I don't know why. :( — forGG, Aug 28 '23 at 13:43
shouldn't , C.RootID be C.ID in the union all part of your cte — siggemannen, Aug 28 '23 at 13:50

score 0 · Answer 1 · answered Aug 28 '23 at 15:08

I believe you should be joining C.ID instead of T.ID. This should now correctly associate each record with its parent's status, based on the minimum status of its children.

DECLARE @data TABLE (
    RowNo INT NOT NULL IDENTITY (1, 1)
  , ID NVARCHAR(15)
  , [ID_Parent] NVARCHAR(15)
  , [Status] INT
);

INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1', NULL, 0);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.1', '1', 0);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.1.1', '1.1', 1);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.1.2', '1.1', 1);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.2', '1', 1);
INSERT INTO @data ([ID], [ID_Parent], [Status])
    VALUES ('1.3', '1', 1);

WITH C AS (
    SELECT T.RowNo
         , T.ID
         , T.[Status]
         , T.ID AS RootID
      FROM @data T
    UNION ALL
    SELECT T.RowNo
         , T.ID
         , T.[Status]
         , C.RootID
      FROM @data T
           INNER JOIN C
                        ON T.ID_Parent = C.ID)
SELECT T.RowNo
     , T.ID
     , T.ID_Parent
     , T.[Status]
     , S.[MINeveryNode]
  FROM @data T
       INNER JOIN (SELECT RootID
                        , MIN([Status]) AS [MINeveryNode]
             FROM C
            GROUP BY RootID) AS S
                    ON T.ID = S.RootID
 ORDER BY T.RowNo
OPTION (MAXRECURSION 0);

Update Parent Status depending on Childs Status

1 Answers1