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

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!

CodePudding user response：

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;

CodePudding user response：

There's the possibility to use a variable.

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

db<>fiddle