t, Sql Server - rextester

Language: Layout:

CREATE TABLE Table1
    ([Id] int, [Name] varchar(29), [ExternalCategoryCode] varchar(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 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;

View schema
Execution time: 0,05 sec, rows selected: 7, rows affected: 8, absolute service time: 0,19 sec