Incremental/Conditional UPDATE - All In One Single UPDATE statement!

Few years ago, a friend of mine working for Ramco Systems 'taught' me a logic of incrementally and conditionally updating a table with help of a few variables. I had to use that logic this AM and it required some brush-up, so now I am going to post the logic and sample code right here for all other SQL programmers...

create table #temp(num int, runningSum int)
INSERT INTO #TEMP VALUES(1, NULL)
INSERT INTO #TEMP VALUES(2, NULL)
INSERT INTO #TEMP VALUES(3, NULL)
INSERT INTO #TEMP VALUES(4, NULL)
INSERT INTO #TEMP VALUES(5, NULL)
INSERT INTO #TEMP VALUES(6, NULL)

declare @RunningSum int
SET @RunningSum = 0

UPDATE #TEMP
SET RunningSum = @RunningSum,
@RunningSum = @RunningSum + num

SELECT * FROM #TEMP

DROP TABLE #Temp

Results:
num         runningSum
----------- -----------
1           1
2           3
3           6
4           10
5           15
6           21

(6 row(s) affected)

Remember: The order of fields in the update statement is very important. Update statement starts by updating the last field first. In this example @RunningSum = @RunningSum + num is update first.




Please consider the environment before printing this blog! Go Green!