CODEWITHSUNDEEP
sql-servert-sql
FrenkyB
FrenkyB

Reputation: 7197

Check hierarchically for parent number

Table that I am using is CADIGR, and column DIGR_KEY1 is always a parent number (DIGR_KEY of the parent) in the row.

I must update CADIGR DIGR_KEY1 (parent number), but first I must check that I am not updating child with one of theirs parent.

I really don't have idea how to do this? Child can have a lot of parents, so check must be done on the whole tree up to the root (where DIGR_KEY1 IS NULL).

Only if the number does not exist as parent number of that child's tree, update is possible.

CREATE TABLE CADIGR
(
DIGR_KEY INT,
DIGR_KEY1 INT
)


INSERT INTO CADIGR (DIGR_KEY, DIGR_KEY1)
VALUES
(1, NULL),
(2,1),
(3,1),
(4,1),
(11,4),
(12,4),
(13,4),
(5,2),
(6,2),
(5,2),
(6,2),
(7,5),
(8,5),
(9,5),
(10,5),
(14,3),
(15,3)

Upvotes: 1

Views: 39

Answers (1)

McNets
McNets

Reputation: 10807

You can use a recursive query like this, can use level to perform operations on each group of childs.

WITH Rec as
(
    SELECT digr_key, digr_key1, 1 AS level
    FROM   CADIGR
    WHERE  digr_key1 is null
    UNION ALL
    SELECT     c.digr_key, c.digr_key1, level = level + 1
    FROM       CADIGR c
    INNER JOIN rec
    ON         c.digr_key1 = rec.digr_key
)
SELECT *
FROM   Rec;
GO

digr_key | digr_key1 | level
-------: | --------: | ----:
       1 |      null |     1
       2 |         1 |     2
       3 |         1 |     2
       4 |         1 |     2
      11 |         4 |     3
      12 |         4 |     3
      13 |         4 |     3
      14 |         3 |     3
      15 |         3 |     3
       5 |         2 |     3
       6 |         2 |     3
       5 |         2 |     3
       6 |         2 |     3
       7 |         5 |     4
       8 |         5 |     4
       9 |         5 |     4
      10 |         5 |     4
       7 |         5 |     4
       8 |         5 |     4
       9 |         5 |     4
      10 |         5 |     4

dbfiddle here

Avoiding duplicated records

WITH DistData as
(
    SELECT DISTINCT digr_key, digr_key1
    FROM   CADIGR
)
    , Rec as
    (
        SELECT digr_key, digr_key1, 1 AS level
        FROM   DistData
        WHERE  digr_key1 is null
        UNION ALL
        SELECT     c.digr_key, c.digr_key1, level = level + 1
        FROM       DistData c
        INNER JOIN rec
        ON         c.digr_key1 = rec.digr_key
    )
    SELECT *
    FROM   Rec;
GO

digr_key | digr_key1 | level
-------: | --------: | ----:
       1 |      null |     1
       2 |         1 |     2
       3 |         1 |     2
       4 |         1 |     2
      11 |         4 |     3
      12 |         4 |     3
      13 |         4 |     3
      14 |         3 |     3
      15 |         3 |     3
       5 |         2 |     3
       6 |         2 |     3
       7 |         5 |     4
       8 |         5 |     4
       9 |         5 |     4
      10 |         5 |     4

dbfiddle here

Upvotes: 2

Related Questions