Hi John, thanks for the suggestion. I wanted to avoid a cycle, but it turns out this is the easiest approach. In the end however I did it in the business code, at least it does the regular auditing now.

You moved to Spain after all?

>Hi Christian, I have a "cycle option" to update records in similar situations in SQL Server, check this code, I tried to adapt it to your tables, maybe it can help you:
>
>

>DECLARE @nBalancepre int, @nRecNum int, @nBalancepost int, @nYear int, @nRun int, @nItem int
>SET @nYear=2019
>SET @nRun=1
>SET @nItem=100
>--get the first balancepre to start calculation, you must adapt this to dinamically change item and year, I assume that run is always 1 to start
>SET @nBalancepre=(SELECT TOP 1 Balancepre from balances where item=@nItem and year=@nYear and run=@nRun)
>--prepare the cycle
>SELECT null kctrl, item, year, run, amount, ROW_NUMBER() OVER(ORDER BY item,year,run) as recnum into #selrows FROM balances where item=@nItem and year=@nYear and run=@nRun;
>WHILE (SELECT count(*) FROM #selrows WHERE isnull(kctrl, 0) = 0) > 0
>BEGIN
>	--record to iterate
>	SET @nRecNum=(SELECT MIN(recnum) FROM #selrows WHERE ISNULL(kctrl, 0) = 0)
>	--get balancepost calculated from current row
>	SELECT @nBalancepost=@nBalancepre+amount, @nYear=year, @nRun=run from #selrows where recnum=@nRecNum
>	--start the update
>	UPDATE balances set balancepre=@nBalancepre, balancepost=@nBalancepost where item=@nItem and year=@nYear and run=@nRun
>	--prepare balancepre for next row
>	SET @nBalancepre=@nBalancepost
>	--update cycle to continue with next
>	UPDATE #selrows set kctrl=1 where recnum=@nRecNum
>END
>--at this moment all the records must be update accordingly
>

>
>Extracted from https://johnharold.wordpress.com/2019/06/27/hacer-ciclos-o-iteraciones-en-sql-server-how-to-make-cycles-or-iterate-in-sql-server/
>
>>I thought this would have been easy, but I got stuck multiple times and don't know if I am on the right track.
>
>>I have a table with forwarded balances. The balances can be edited, so the forwarding should correct mistakes, but starting from a certain point. So I cannot make a total sum.
>
>Item Year Run Amount Balancepre Balancepost
>100 2019 1 100 1000 1100
>100 2019 2 100 1100 1200
>100 2019 4 50 1200 1250
>100 2019 5 100 0 0
>
>So in run #5 it got off, and I want to recalculate balances starting from the first run in 2019.
>So I thought I need to select the first all except the first occurrences of year 2019 and then add the sum amounts with lower run numbers. But I got stuck with the syntax. I also wonder if both balances can be updated at once, balancepre and balancepost, or I need to create two separate updates.
>I would be glad to get any hint as to how I should approach this query.