T-SQL sequential updating with two columns

Question

I have a table created by:

CREATE TABLE table1 
(
    id INT,
    multiplier INT,
    col1 DECIMAL(10,5)
)

INSERT INTO table1 
VALUES (1, 2, 1.53), (2, 3, NULL), (3, 2, NULL),
       (4, 2, NULL), (5, 3, NULL), (6, 1, NULL)

Which results in:

id  multiplier  col1
-----------------------
1   2           1.53000
2   3           NULL
3   2           NULL
4   2           NULL
5   3           NULL
6   1           NULL

I want to add a column col2 which is defined as multiplier * col1, however the next value of col1 then updates to take the previous calculated value of col2.

The resulting table should look like:

id  multiplier  col1        col2
---------------------------------------
1       2         1.53000     3.06000
2       3         3.06000     9.18000
3       2         9.18000    18.36000
4       2        18.36000    36.72000
5       3        36.72000   110.16000
6       1       110.16000   110.16000

Is this possible using T-SQL? I've tried a few different things such as joining id to id - 1 and have played around with a sequential update using UPDATE and setting variables but I can't get it to work.

user_1144035 · Answer

A recursive CTE might be the best approach. Assuming your ids have no gaps:

with cte as (
      select id, multiplier, convert(float, col1) as col1, convert(float, col1 * multiplier) as col2
      from table1
      where id = 1
      union all
      select t1.id, t1.multiplier, cte.col2 as col1, cte.col2 * t1.multiplier
      from cte join
           table1 t1
           on t1.id = cte.id + 1
     )
select *
from cte;

Here is a db<>fiddle.

Note that I converted the destination type to float, which is convenient for this sort of operation. You can convert back to decimal if you prefer that.

user_10676716 · Answer

Basically, this would require an aggregate/window function that computes the product of column values. Such set function does not exists in SQL though. We can work around this with arithmetics:

select
    id,
    multiplier,
    coalesce(min(col1) over() * exp(sum(log(multiplier)) over(order by id rows between unbounded preceding and 1 preceding)), col1) col1,
    min(col1) over() * exp(sum(log(multiplier)) over(order by id)) col2
from table1

Demo on DB Fiddle:

id | multiplier |   col1 |   col2
-: | ---------: | -----: | -----:
 1 |          2 |   1.53 |   3.06
 2 |          3 |   3.06 |   9.18
 3 |          2 |   9.18 |  18.36
 4 |          2 |  18.36 |  36.72
 5 |          3 |  36.72 | 110.16
 6 |          1 | 110.16 | 110.16

This will fail if there are negative multipliers.

If you wanted an update statement:

with cte as (
    select col1, col2,
        coalesce(min(col1) over() * exp(sum(log(multiplier)) over(order by id rows between unbounded preceding and 1 preceding)), col1) col1_new,
        min(col1) over() * exp(sum(log(multiplier)) over(order by id)) col2_new
    from table1
)
update cte set col1 = col1_new, col2 = col2_new

T-SQL sequential updating with two columns

2 Answers