TSQL Update Column B with Column A's Value that is also being updated

user_2784325 0
Viewed 35

I am processing 155+ Million rows in a table and doing it in batches for performance and ran into a situation where the value in a column didn't get updated as expected. I know I can solve it a couple of different ways but I wanted to see if someone knows how to do it in a single update statement.

Below is a test case:

DECLARE @TempIds TABLE (    
    ColA int,
    ColB int
) 

--seed the values for a test
INSERT INTO @TempIds (ColA,ColB) VALUES (1, 2)

--do the update
UPDATE @TempIds SET ColA = (3+5), ColB = (1-ColA) 

--see the results
SELECT * FROM @TempIds

The results of the above is:

ColA    ColB
8       0

Desired Outcome is

ColA    ColB
8       -7

The update statement is using the current value for ColA which is "1" when updating ColB instead of using the final value of ColA being "8".

I know that I can solve this by doing one of the following:

UPDATE @TempIds SET ColA = (3+5)
UPDATE @TempIds SET ColB = (1-ColA) 
    
--see the results
SELECT * FROM @TempIds
    
OR 
    
UPDATE @TempIds SET ColA = (3+5), ColB = 1-(3+5)
    
--see the results
SELECT * FROM @TempIds

Either one of the above will result in the following output:

ColA    ColB
8       -7

This is a simplified version as the actual query has a lot of computations and formulas to calculate the value for ColA.

I am trying to avoid two update statements or the need to repeat the formula for ColA in the ColB formula.

Thanks in advance!

tsql
2 Answers

Could you possibly make use of an Updatable CTE by first creating a new column A and then referencing it:

with updateme as (
    select *, (3+5) as NewA
    from @TempIds
)
update updateme set colA = NewA, colb = 1-NewA;

select * from @TempIds;
user_153326500

There's the possibility to use a variable.

DECLARE @newcola integer;
UPDATE @tempids
       SET @newcola = cola = 3 + 5,
           colb = 1 - @newcola;

db<>fiddle

user_96614240
Related