title: Sql server CTE get parent's children then update children with parent value
body: <p>How do I loop through all children and update one column for all the nodes? For example if I am on the parent level with column "OrganisationLevelID". If I update the "OrganisationLevelID" on the parent row it should propogate to the children hence all children of parent as well as the childrens' children should then have the same value in "OrganisationLevelID".</p>
  
  <p><strong>I have the following cte</strong>: </p>
  
  <pre><code> WITH cte as
  (select h.Name, h.ID, h.OrganisationLevelID, h.parentId
  From organisations h
  WHERE ID = '3eea8c17-1bfd-46d5-9ea9-2d11652d23a4'
  UNION ALL 
  select p.Name, p.ID, p.OrganisationLevelID, p.parentId
  FROM organisations p
  INNER JOIN cte c ON c.Id = p.parentId)
  SELECT *  FROM cte
  </code></pre>
  
  <p>This gets the parent child hierarchy and works fine.</p>
  
  <p>How do I update the children values so that they all have the parent's "OrganisationLevelID"?</p>
  
  <pre><code> WITH cte as
      (select h.Name, h.ID, h.OrganisationLevelID, h.parentId
      From organisations h
      WHERE ID = '3eea8c17-1bfd-46d5-9ea9-2d11652d23a4'
      UNION ALL 
      select p.Name, p.ID, p.OrganisationLevelID, p.parentId
      FROM organisations p
      INNER JOIN cte c ON c.Id = p.parentId),
    cteupdate as(**Some magic here**)
  </code></pre>