使用子记录数据(同表)更新父记录的字段 [英] Updating a Parent Record's field using Child's Record Data (Same Table)

问题描述

我有一个表，其中存在父/子关系，其中每个子项的 ExternalCategoryCode 列数据需要连接到父项 ExternalCategoryCode 中.

以下是一些已损坏的示例数据，其中父记录 (Id=96)ExternalCategoryCode 是 NULL.关系是通过 ParentId 字段:

Id |Name |ExternalCategoryCode|ParentId---|------------------------------|-------------------|---------96 |健康与个人发展|空 |空值---|------------------------------|-------------------|---------97 |健康 |H1*;H2* |96---|------------------------------|-------------------|---------98 |个人发展 |P1;P2;P3* |96---|------------------------------|-------------------|---------99 |其他健康 |哦* |96---|------------------------------|-------------------|---------

数据应如下所示:

Id |Name |ExternalCategoryCode |ParentId---|------------------------------|---------------------|---------96 |健康与个人发展|H1*;H2*;P1;P2;P3*;OH*|空值---|------------------------------|---------------------|---------97 |健康 |H1*;H2* |96---|------------------------------|---------------------|---------98 |个人发展 |P1;P2;P3* |96---|------------------------------|---------------------|---------99 |其他健康 |哦* |96---|------------------------------|---------------------|---------

1. 后代级别可以是任何深级别，但深子级别后代需要更新到主要的父级ExternalCategoryCode.取下面的最后两条记录 (Id=100和 Id=101) 都链接到 ParentId 97.因为 Id=97ParentId IS NOT NULL，我们应该继续向上直到我们查找 ParentId 为 NULL(最高父级)的记录.
2. 另一个问题是，最上层父级的 ExternalCategoryCode 是否可以包含重复项?下面的示例显示 M2* 两次.这很好，因为我们的业务逻辑类库会过滤掉找到的重复项.

3. 如果记录在其 ParentId(即是子项)中具有链接但具有 NULL ExternalCategoryCode，则这些代码可以忽略.

   Id |Name |ExternalCategoryCode |ParentId---|------------------------------|---------------------|---------96 |健康与个人发展|H1*;H2*;P1;P2;P3*;OH*|空值|||||M1;M2*;M2*;M3* |---|------------------------------|---------------------|---------97 |健康 |H1*;H2* |96---|------------------------------|---------------------|---------98 |个人发展 |P1;P2;P3* |96---|------------------------------|---------------------|---------99 |其他健康 |哦* |96---|------------------------------|---------------------|---------100|医药 |M1;M2* |97---|------------------------------|---------------------|---------101|其他医药 |M2*;M3* |97---|------------------------------|---------------------|---------102|其他药物 2 |空 |97---|------------------------------|---------------------|---------

表中还有其他父/子损坏.我该怎么做:

1. 编写此脚本以将相关表格向下并连接父记录 ExternalCategoryCode 与子ExternalCategoryCode 数据?

2. 列出更新的父记录.通常在使用 UPDATED 时，这只是显示这样的东西，我想报告这个:

   (1 行受影响)

我使用的技术:

• SQL Server

解决方案

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

解决方案

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

这篇关于使用子记录数据(同表)更新父记录的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！