Updating a Parent Record's field using Child&#

问题:

I have a table whereby there is a Parent/Child relationship, where each of the children's ExternalCategoryCode column data needs to be concatenated into the parents ExternalCategoryCode.

Here is some example data that is corrupted, whereby the Parent record (Id=96) ExternalCategoryCode is NULL. The relationship is via the ParentId field:

Id |Name                          |ExternalCategoryCode|ParentId
---|------------------------------|--------------------|--------- 
96 | Health & Personal Development| NULL               | NULL
---|------------------------------|--------------------|---------
97 | Health                       | H1*;H2*            | 96
---|------------------------------|--------------------|---------
98 | Personal Development         | P1;P2;P3*          | 96
---|------------------------------|--------------------|---------
99 | Other Health                 | OH*                | 96
---|------------------------------|--------------------|---------

The data should look like this:

Id |Name                          |ExternalCategoryCode  |ParentId
---|------------------------------|----------------------|--------- 
96 | Health & Personal Development| H1*;H2*;P1;P2;P3*;OH*| NULL
---|------------------------------|----------------------|---------
97 | Health                       | H1*;H2*              | 96
---|------------------------------|----------------------|---------
98 | Personal Development         | P1;P2;P3*            | 96
---|------------------------------|----------------------|---------
99 | Other Health                 | OH*                  | 96
---|------------------------------|----------------------|---------

EDITs:

1. The descendant level can be any levels deep, but the deep children descendants need updating to the main Parent's ExternalCategoryCode. Take the last two records below (Id=100 and Id=101) both link to a ParentId 97. Because Id=97 ParentId IS NOT NULL, we should continue up the tree until we find a record with a ParentId of NULL (Top most Parent).
2. Another question was regarding can the upper most parent's ExternalCategoryCode contain duplicates? Example below shows M2* twice. This is fine, because our business logic class libraries filter out duplicates if it finds any.

3. If a record has a link in its ParentId(i.e. is a child) but has a NULL ExternalCategoryCode, these codes can be ignored.

   Id |Name                          |ExternalCategoryCode  |ParentId
   ---|------------------------------|----------------------|--------- 
   96 | Health & Personal Development| H1*;H2*;P1;P2;P3*;OH*| NULL
      |                              |                      |
      |                              | M1;M2*; M2*;M3*      |
   ---|------------------------------|----------------------|---------
   97 | Health                       | H1*;H2*              | 96
   ---|------------------------------|----------------------|---------
   98 | Personal Development         | P1;P2;P3*            | 96
   ---|------------------------------|----------------------|---------
   99 | Other Health                 | OH*                  | 96
   ---|------------------------------|----------------------|---------
   100| Medicine                     | M1;M2*               | 97
   ---|------------------------------|----------------------|---------
   101| Other Medicine               | M2*;M3*              | 97
   ---|------------------------------|----------------------|---------
   102| Other Medicine 2             | NULL                 | 97
   ---|------------------------------|----------------------|---------
   

There are other Parent/Child corruptions in the table as well. How do I:

1. Script this to down the table in question and concatentate the parent records ExternalCategoryCode with child ExternalCategoryCode data?

2. List the Parent records which are updated. Usually when using UPDATED, this just shows something like this, whereby I would like to report this:

   (1 row(s) affected)

Techology Im using:

• SQL Server

回答1:

SQL DEMO First concatenate all code from same parent, and then update.

WITH superParent as (
    SELECT [Id], [Id] as [topParent], [Name], [ExternalCategoryCode], [ParentId]
    FROM Table1 t
    WHERE [ParentId] is NULL
    UNION ALL
    SELECT t.[Id], sp.[topParent], t.[Name], t.[ExternalCategoryCode], t.[ParentId]    
    FROM Table1 t
    JOIN superParent sp
      ON sp.[id] = t.[ParentId]
),  
combine as (
    Select distinct ST2.[topParent], 
        (
            Select ST1.[ExternalCategoryCode] + ';' AS [text()]
            From superParent ST1
            Where ST1.[topParent] = ST2.[topParent]
            ORDER BY ST1.[Id]
            For XML PATH ('')
        ) [External]
    From superParent ST2
    WHERE ST2.[ParentId] IS NOT NULL    
)    
UPDATE T
SET T.[ExternalCategoryCode] = C.[External]
FROM Table1 T
JOIN combine C
  ON T.[Id] = c.[topParent];

SELECT *
FROM Table1;

OUTPUT using recursive cte assign the top parent to each child. Then use the XML PATH to concatenate all CategoryCode

回答2:

This one is tough. Try this.

CREATE TABLE #Table1
    ([Id] int, [Name] varchar(29), [ExternalCategoryCode] nvarchar(max), [ParentId] varchar(4))
;

INSERT INTO #Table1
    ([Id], [Name], [ExternalCategoryCode], [ParentId])
VALUES
    (96, 'Health & Personal Development', NULL, NULL),    
    (97, 'Health', 'H1*;H2*', '96'),
    (98, 'Personal Development', 'P1;P2;P3*', '96'),
    (99, 'Other Health', 'OH*', '96'),
    (100, 'Medicine', 'M1;M2*', '97'),
    (101, 'Other Medicine', 'M2*;M3*', '97'),
    (102, 'Other Medicine 2', NULL, '97')
;

WITH cte AS (
    SELECT
        Id
        , ParentId AS Direct_Parent
        , Id AS Orig_Parent
        , ExternalCategoryCode
    FROM
        #Table1
    WHERE
        ParentId IS NULL

    UNION ALL

    SELECT
        t1.Id
        , t1.ParentId
        , cte.Orig_Parent
        , t1.ExternalCategoryCode
    FROM
        #Table1 t1
        JOIN cte ON t1.ParentId = cte.Id
), tmp1 AS (
    SELECT DISTINCT
        Orig_Parent
        , ExternalCategoryCode
    FROM
        cte
    WHERE
        Id <> Orig_Parent
        AND ExternalCategoryCode IS NOT NULL
), tmp2 AS (  -- If there are too many children, this one might be needed, other wise, just use tmp1 in tmp3 below
    SELECT DISTINCT
        Orig_parent
    FROM
        tmp1
), tmp3 AS (
    SELECT
        tmp2.Orig_Parent,
        (
        SELECT CONCAT(tmp1.ExternalCategoryCode,',')
        FROM
            tmp1
        WHERE
            tmp1.Orig_parent = tmp2.Orig_Parent
        FOR XML PATH(''),type).value('.','NVARCHAR(MAX)')
        AS New_string 
    FROM
        tmp2
)
UPDATE tab
SET ExternalCategoryCode = tmp3.New_string
FROM #Table1 tab JOIN tmp3 ON tab.Id = tmp3.Orig_Parent

SELECT *
FROM
    #Table1;


DROP TABLE #Table1;