如何用SQL Server CTE同步更新父节点及所有子节点的OrganisationLevelID

Hey there! To get that parent's OrganisationLevelID synced down to all its child and descendant nodes, you just need to tweak your recursive CTE to carry the parent's target value through the hierarchy, then use that to update the table. Here's how to do it:

Modified Code #

WITH cte AS (
    -- Grab the parent node and its OrganisationLevelID (this is the value we'll sync everywhere)
    SELECT 
        h.ID,
        h.ParentID,
        h.OrganisationLevelID AS TargetLevelID
    FROM organisations h 
    WHERE ID = '3eea8c17-1bfd-46d5-9ea9-2d11652d23a4'
    
    UNION ALL
    
    -- Recursively get all children, passing down the parent's TargetLevelID
    SELECT 
        p.ID,
        p.ParentID,
        c.TargetLevelID  -- Use the parent's value instead of the child's current one
    FROM organisations p 
    INNER JOIN cte c ON c.ID = p.ParentID
)
-- Update every node in the hierarchy with the parent's value
UPDATE o
SET o.OrganisationLevelID = c.TargetLevelID
FROM organisations o
INNER JOIN cte c ON o.ID = c.ID
-- Uncomment this line if you don't want to re-update the parent node (since it's already the source)
-- WHERE o.ID != '3eea8c17-1bfd-46d5-9ea9-2d11652d23a4';

Breakdown of What's Changed #

• Anchor Member: We added TargetLevelID to capture the parent's OrganisationLevelID—this is the value we're propagating down.
• Recursive Member: Instead of pulling the child's own OrganisationLevelID, we use the TargetLevelID from the parent in the CTE. This ensures every descendant inherits the parent's value, not their existing one.
• UPDATE: We join the original table to our CTE and set each node's OrganisationLevelID to the synced TargetLevelID. The optional WHERE clause lets you skip re-updating the parent if it's already set to the value you want.

Bonus: Set Parent Value + Sync in One Step #

If you need to update the parent's OrganisationLevelID and sync it to children all at once, just hardcode the new value in the anchor:

WITH cte AS (
    SELECT 
        h.ID,
        h.ParentID,
        'YourNewLevelID' AS TargetLevelID  -- Replace with your actual new value
    FROM organisations h 
    WHERE ID = '3eea8c17-1bfd-46d5-9ea9-2d11652d23a4'
    
    UNION ALL
    
    SELECT 
        p.ID,
        p.ParentID,
        c.TargetLevelID
    FROM organisations p 
    INNER JOIN cte c ON c.ID = p.ParentID
)
UPDATE o
SET o.OrganisationLevelID = c.TargetLevelID
FROM organisations o
INNER JOIN cte c ON o.ID = c.ID;

This will update the parent's value first, then push that same value to all its descendants in a single statement.

内容的提问来源于stack exchange，提问作者p0enkie