How to update a select with sub queries that contain Group by and order by

Question

Physical Table

Registro IDBanco DataMovimento Credito Debito SaldoAtual Ordenado
    1      26     2020-07-05   500,00   0,00    0,00        5
    2      26     2020-07-03    0,00    10,00   0,00        3
    3      26     2020-07-06    0,00    20,00   0,00        6
    4      26     2020-07-01    0,00    30,00   0,00        1
    5      26     2020-07-06    0,00    40,00   0,00        7
    6      26     2020-07-01    0,00    50,00   0,00        2
    7      26     2020-07-08    0,00    60,00   0,00        8
    8      26     2020-07-04    0,00    70,00   0,00        4
    9      26     2020-07-11    0,00    80,00   0,00        9
   10      26     2020-07-15    0,00    80,00   0,00       11
   11      26     2020-07-11    0,00   100,00   0,00       10

When I run this select I get the result of the query in the table below in the column "Sum".

SELECT t1.Ordenado, SUM(t2.SaldoInicial + T2.Credito - T2.Debito) as Soma
FROM tbl_SaldoAtual AS t1
INNER JOIN tbl_SaldoAtual AS t2 on t1.Ordenado >= t2.Ordenado
GROUP BY  t1.Ordenado
ORDER BY t1.Ordenado  ASC

Ordenado  Soma
    1   -30.00
    2   -80.00
    3   -90.00
    4  -160.00
    5   340.00
    6   320.00
    7   280.00
    8   220.00
    9   140.00
   10    40.00
   11   -40.00

I am unable to find a way to update the "Sum" column of the query to the physical table in the "Saldoatual" column, where the "Ordered" column will be the column of Group by and Order by as selected above.

I’ve tried with CTE, Temporary table, Update with sub queries, but no success. If anyone can shed a light.

Answer 1

Solution found to the above problem.

UPDATE tbl_SaldoAtual
SET SaldoAtual = Soma
FROM
(SELECT TOP(15) WITH TIES t1.Ordenado, SUM(t2.SaldoInicial + T2.Credito - T2.Debito) as Soma
FROM tbl_SaldoAtual AS t1
INNER JOIN tbl_SaldoAtual AS t2 on t1.Ordenado >= t2.Ordenado
GROUP BY  t1.Ordenado
ORDER BY t1.Ordenado  ASC) TS
WHERE tbl_SaldoAtual.Ordenado = TS.Ordenado