SQL Server Update Column Comparing Cross-String Value

Question

I have a requirement to update column3 of the following table by cross-checking value2 with the next row value1

If equal, then value3 = value1 * value2 and if not value3 = value1

CREATE TABLE #tmpValue1(id INT IDENTITY(1,1), value1 FLOAT, value2 FLOAT, value3 FLOAT)

INSERT INTO #tmpValue1(value1, value2) VALUES 
(1, 2), (2,3), (3,4), (4,5),(6,7),(7,8),(8,9)

Table # tmpValue1 will look like:

id  value1  value2  value3 (expected output)
1   1       2       1    
2   2       3       4
3   3       4       9
4   4       5       16
5   6       7       6
6   7       8       49
7   8       9       64

Above, in value3, updated from 1 in the first, because 2 lines of Value2 is first compared with 2 values ​​of 1 line in the second, so it will start updating from the second.

Note. The values ​​of Value1 and Value2 are just a sample and real, it may be different.

Answer 1

We can simply do this using the LEFT JOIN as shown below:

UPDATE t1 SET t1.value3 = (ISNULL(t2.value2,1) * t1.value1)
FROM #tmpValue1 t1
LEFT JOIN #tmpValue1 t2 ON t1.id = t2.id+1
    AND t1.value1 = t2.value2

, .

Answer 2

LAG... , , .

select 
    ID, 
    Value1, 
    Value2,
    case 
        when lag(value2) over (order by ID) = value1 then lag(value2) over (order by ID) * value1 
        else value1 end as Value3
from #tmpValue1

ID  Value1  Value2  Value3
1   1       2       1
2   2       3       4
3   3       4       9
4   4       5       16
5   6       7       6
6   7       8       49
7   8       9       64