Updating hierarchical data using hierarchyId

Question

Updating hierarchical data using hierarchyId

Navigation

#1 by (-1 votes)

2

Hello. I have the following table in SQL Server 2008

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

Is there any way to do this without using looping (and recursive CTEs)?

sql

asked by anonymous 08.07.2015 / 23:23

1 answer

Mobile Page in Chrome Simulator Error 403 - the web server is configured not to list the content

score -1 · Answer 1

I think you want something of that kind, am I right?

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;