Fever Fever - 1 month ago 11
SQL Question

SQL Server Update column comparing cross row's value

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.

Answer

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.