Untitled

Update column value of one row from other rows
sno name   pid amount total
1   Arif    0   100    null
2   Raj     1   200    null
3   Ramesh  2   100    null
4   Pooja   2   100    null
5   Swati   3   200    null
6   King    4   100    null

WITH hierarchified AS (
  SELECT
    sno,
    amount,
    hierarchyID = CAST(sno AS varchar(500))
  FROM yourTable
  WHERE pid = 0
  UNION ALL
  SELECT
    t.sno,
    t.amount,
    hierarchyID = CAST(h.hierarchyID + '/' + RTRIM(t.sno) AS varchar(500))
  FROM yourTable t
    INNER JOIN hierarchified h ON t.pid = h.sno
)
UPDATE yourTable
SET total = t.amount + ISNULL(
  (
    SELECT SUM(amount)
    FROM hierarchified
    WHERE hierarchyID LIKE h.hierarchyID + '/%'
  ),
  0
)
FROM yourTable t
INNER JOIN hierarchified h ON t.sno = h.sno;