CODEWITHSUNDEEP
sql-server-2005
Vikas Borkar
Vikas Borkar

Reputation: 11

Update column value of one row from other rows

I have the following table:

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

I want total of each person such that it gives total sum of amount of its descendants. For ex.

Upvotes: 1

Views: 440

Answers (1)

Andriy M
Andriy M

Reputation: 77657

You could try something like this:

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;

Note that this query (which you can try on SQL Fiddle) would probably not be very efficient on a large dataset. It might do as a one-off query, and then it would likely be better to organise updating the totals each time the table is updated, i.e. using triggers.

Upvotes: 2

Related Questions