SQL Server Update column comparing cross row's value

4
votes

I have requirement to update column3 of following table by cross checking the value of value2 with next row of 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 be as:

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 with 1 in first because 2 of Value2 row first is comparing to 2 of value1 of row second so it will start updating with second.

Note: Value1 and Value2 is just sample and is real it can be different.

2
sqlsql-serverjoin

2 Answers

4
votes

We can simply do it by using LEFT JOIN as 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

We should use id which is identity column and is beneficial for performing such an operation.

1
votes

You can use LAG... though your expected output seems to have some errors in it.

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

RESULTS

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