2

One of our application devs came to me with an interesting question. In the following code, you'll see that the update statement is assigning the current column values to two variables, but also updating those same columns to new values.

The question was, is it guaranteed that the variables @oStart and @oFinish will be assigned to the old values of [Start] and [Finish] and not the new values? Under testing, this has been the case, however, I'd like to confirm this is the guaranteed behaviour.

I expect the answer is "no - it's not guaranteed", and ultimately, it's down to the decision the optimiser takes. Regardless, this kind of ambiguous code is to be discouraged anyway (there are more elegant and readable ways of writing it), but would be interested to hear everyone's thoughts.

-- Parameters
DECLARE @StartDate smalldatetime = '2022-01-01',
       @EndDate smalldatetime = '2022-12-31';

-- Body
DECLARE @oStart smalldatetime,
       @oFinish smalldatetime;

UPDATE  [dbo].[MyTable]
SET     @oStart = [Start],
       @oFinish = [Finish],
       [Start] = @StartDate,
       [Finish] = @EndDate
WHERE   [ID] = 12;
2
  • 2
    If you've never seen it, the standard way to swap the contents of two columns in SQL is UPDATE tab SET a=b, b=a, precisely because the right hand sides are effectively computed before any assignments take place. Commented May 5, 2023 at 7:59
  • 1
    FWIW, those date literals are wrong for SQL Server. If you have a date-only value, with no time component, you should omit the hyphens: 20220101 and 20221231. Additionally, for a full-year range like that, you want to define it using exclusive upper bound for the range for the day after. That said, this tends to confuse lay people who do data entry for the ranges, so it's also common to do the check for the upper range for the day after the stored value :/ Commented May 5, 2023 at 14:31

2 Answers 2

8

Yes this is documented. See the documentation for @variable in the UPDATE syntax.

SET @variable = column ... sets the variable to the pre-update value of the column.

(As contrasted in the documentation to SET @variable = column = expression which will set the variable to the value after the column = expression assignment).

DECLARE @oldStart  SMALLDATETIME,
        @oldFinish SMALLDATETIME,
        @newStart  SMALLDATETIME,
        @newFinish SMALLDATETIME;

DECLARE @MyTable TABLE
  (
     ID     INT PRIMARY KEY,
     Start  SMALLDATETIME,
     Finish SMALLDATETIME
  )

INSERT @MyTable
VALUES(12, '1900-01-01', '1900-01-31');

UPDATE @MyTable
SET    @oldStart = [Start],
       @oldFinish = [Finish],
       @newStart = [Start] = '2022-01-01',
       @newFinish = [Finish] = '2022-12-31'
WHERE  [ID] = 12;

SELECT @oldStart  AS [@oldStart],
       @oldFinish AS [@oldFinish],
       @newStart  AS [@newStart],
       @newFinish AS [@newFinish]; 

Returns

@oldStart @oldFinish @newStart @newFinish
1900-01-01 00:00:00 1900-01-31 00:00:00 2022-01-01 00:00:00 2022-12-31 00:00:00

Assuming ID is the primary key so the UPDATE is guaranteed to affect at most one row then personally I think this syntax is fine if you need to assign that value to a SQL variable.

It beats having to use the OUTPUT ... INTO clause to store these values somewhere and then a subsequent SELECT from that to assign to the variable.

5

The SQL standard stipulates that

The <update source> of each <set clause> contained in SCL [Set Clause List] is effectively evaluated for each row of [table] T before any row of T is updated

(emphasis mine). In your case it means that, according to the standard, @oStart and @oFinish must have the values of [Start] and [Finish], respectively, as they exist before the update.

Now, whether SQL Server complies with the standard in this regard is a different question.

1
  • SQL Server does follow the standard here. Commented May 5, 2023 at 14:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.