Updating hierarchical data using hierarchyId

Question

Hello. I have the following table in SQL Server 2008

The goal is to update the Value field on a child and accumulate in the parent. For example: If in the id record 12 I put the value 31 in the value field, your direct parent (8) must receive the value 31. If in the id record 11 I put 25, your direct parents (6,4,3,1) should receive another 25 in the value field.

Is there any way to do this without using loopings (and recursive Ctes)?

Answer 1

I think you want something like that, I’m sure?

SET SQL_SAFE_UPDATES=0;
UPDATE     Atividades mae
LEFT JOIN  Atividades filho on
           (mae.id_mae = filho.id)
SET        mae.valor = mae.valor + 31,
           filho.valor = 31
WHERE      filho.id = 12;