tsql - SQL Server : UPDATE MyTable SET col1 = value, col2 = col1

Question

I have a table col1 float, col2 float with the following data:

1,2

3,4

After the query:

UPDATE MyTable SET col1 = 100, col2 = col1

is executed, the rows are:

100,1

100,3

Obviously, the old, not updated value of col1 is used, when col2 is being updated. Is it possible to update the col2 with the new value, referring col1 or I have to write the value (100) twice? I want to use auto-generated queries and it's easier to keep formulas.

Regards,

Answer 1

Here's another alternative to try:

DECLARE @x float;

UPDATE MyTable
SET
  @x = col1 = formula,
  col2 = @x * …
OPTION (MAXDOP 1)

or:

DECLARE @x float;

UPDATE MyTable
SET
  @x = formula,
  col1 = @x,
  col2 = @x * …
OPTION (MAXDOP 1)

OPTION (MAXDOP 1) is there to ensure the sequential order of evaluation of assignments.