In Sql Server, I have a simple table that store amount and balance like this:
ID | Date | Amount | Balance
-------------------------------------
101 | 1/15/2017 | 3.00 | 67.50
102 | 1/16/2017 | 5.00 | 72.50
103 | 1/19/2017 | 9.00 | 81.50
104 | 1/20/2017 | -2.00 | 79.50
If I changed a amount of a record, I need to update all the balance after that record.
ID | Date | Amount | Balance
-------------------------------------
101 | 1/15/2017 | 3.00 | 67.50
102 | 1/16/2017 | *5.02* | *72.52*
103 | 1/19/2017 | 9.00 | *81.52*
104 | 1/20/2017 | -2.00 | *79.52*
By now I have more than 100 million records in this table. To do this work, I don't want to use sql cursor or client program, it will submit plenty Update statements and take several hours to finish. Is it can be done in one sql statement to re-calculate the balance of entire table?
You can easily do it in a single SQL statement using SUM() OVER.
eg
WITH tot as (select ID, SUM(Amount) as balance OVER (order by ID)
UPDATE Tab
SET Balance = t.Balance
FROM YOURTABLE tab
JOIN Tot
ON tot.id = tab.id
If the balance is reset by any other column then use this as a partition by clause and include in the join. Now if you are inserting a new row you can simply run this update query with a where clause.