SQL Server : UPDATE MyTable SET col1 = value, col2

问题:

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,

回答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.

回答2:

Your UPDATE statement sees the before version of the row, not the after version. If you're auto-generating queries, isn't it just as easy to have the code dump the formula twice, as it is to pick some other column to reference? Then again, what is the point of storing the same value twice? You could always generate two UPDATE statements too as an alternative.

回答3:

Why don't you use a scalar function? That way you can encapuslate your code into one place and call it for each column you wish to update. You can even pass in paramaters to it.

CREATE FUNCTION [dbo].[doStuff]
(
    @myInput
)
RETURNS INT 
WITH EXECUTE AS CALLER
AS
BEGIN
DECLARE @VAL INT

SET @VAL = @myInput * 100

RETURN @VAL    
END

Then you do this:

UPDATE MyTable SET col1 = [dbo].doStuff(1), col2 = [dbo].doStuff(2)

回答4:

You could always have your code generate two update statements:

UPDATE MyTable SET Col1 = 100;
UPDATE MyTable SET Col2 = Col1;

But then if your queries are actually realistic and you have a WHERE clause, you have to repeat the WHERE clause twice, so you haven't really gained anything.

回答5:

You can do the calculation in a cross apply and use the value in the update to avoid adding the same logic in more than one place.

declare @T table (Col1 float, Col2 float)

insert into @T values(1, 2),(3, 4)

update T set
  Col1 = C.Value,
  Col2 = C.Value * pi() / 4
from @T as T
  cross apply (select 100*100) as C(Value)