Setting variable inside table update statement

Question

TempTable has columns RunningTotal and ClientCount, we also have @RunningTotal variable declared and set to 0.

Can someone please explain what does this line do ?

UPDATE Temptable 
SET @RunningTotal = RunningTotal = @RunningTotal + ClientCount

Answer 1

Never seen this construct before, but it seems to work like this. It fills column RunningTotal with a cumulative total of ClientCount.

Say we start with a table with just ClientCount filled in:

CREATE TABLE dbo.Temptable (ClientCount int, RunningTotal int)
INSERT INTO Temptable (ClientCount) VALUES (5), (4), (6), (2)
SELECT * FROM Temptable

ClientCount RunningTotal
----------- ------------
5           NULL
4           NULL
6           NULL
2           NULL

And then run the update statement:

DECLARE @RunningTotal int = 0
UPDATE Temptable SET @RunningTotal = RunningTotal = @RunningTotal + ClientCount
SELECT * FROM Temptable

ClientCount RunningTotal
----------- ------------
5           5
4           9
6           15
2           17

As you can see, each value of RunningTotal is the sum of all ClientCount values from the current and any preceding records.

The downside is, you have no control in which order the records are processed. Which makes me wonder whether this is a recommended approach in a production environment.

Please check here for a deeper discussion: Calculate a Running Total in SQL Server