0

can somebody tell me why the value of Val is 5 (and not 10) after this update?

create table #tmp(Val int)
insert into #tmp values(1)

declare @i int = 10
update #tmp set Val = @i,  @i = 5

select * from #tmp
Máté Farkas
  • 146
  • 2
  • 2
    regardless of why it happens, why would you even try to do that in a single statement? makes no sense – Mitch Wheat Jun 20 '22 at 07:42
  • 2
    see https://stackoverflow.com/questions/2203202/sql-update-order-of-evaluation – Mitch Wheat Jun 20 '22 at 07:44
  • 1
    You can't make assumptions that SQL works like a procedural language, afaik the *declarative* nature of the SQL language means assumptions like this should not be relied upon. – Stu Jun 20 '22 at 07:50
  • It's just a simplified example. Originally I made come calculations based on other columns into a variable but it does not run in order of appearance of SET clause. I cannot find the logic of order of execution. – Máté Farkas Jun 20 '22 at 07:56
  • 1
    "I cannot find the logic of order of execution." - don't rely on the order of execution. At a minimum, write code with the principle of least surprise for the next maintainer. – Mitch Wheat Jun 20 '22 at 08:00
  • 2
    If you can't find any documentation stating any particular order is guaranteed then it isn't guaranteed – Martin Smith Jun 20 '22 at 08:08
  • 1
    It's not a problem that's stopping you though is it, just evaluate your variable assignment expressions separately and just use the update statement to.... update. – Stu Jun 20 '22 at 08:20
  • 1
    I can't see any documentation in SQL Server that explicitly states this behaviour occurs, however, the documentation *does* state that in an `UPDATE` statement `@Variable = Column` will assign the value of the column **before** the update is applied, and `@Variable = Column = expression` assign the value *after* the update. As, therefore, the syntax you have is `@Variable = Expression` it could be a logical assumption that it is assigning the variable *before* the column(s) are `UPDATE`d, and thus is assigned `5` to the variable first, before the column is `UPDATE`d with the variable's value. – Thom A Jun 20 '22 at 08:27
  • 3
    I can assure you that all of the right hand side values are computed before assigning to the left hand side of each SET expression in an update in SQL Server. (per ANSI SQL) – Conor Cunningham MSFT Jun 20 '22 at 14:53
  • @ConorCunninghamMSFT Oddly enough I can't find anywhere in the SQL Server documentation online that states this as fact, although I believe it's in ANSI-92 at least – Charlieface Jun 26 '22 at 01:29
  • @Charlieface it is part of ANSI SQL, but the spec is not public and it is not easy for the average human to read. I assure you that all RHS values are computed before assignment to the LHS in SQL Server. double-pinky promise. – Conor Cunningham MSFT Jun 26 '22 at 19:29
  • @ConorCunninghamMSFT I know that as well as you, but you'd expect it would be in the [docs for `UPDATE`](https://learn.microsoft.com/en-us/sql/t-sql/queries/update-transact-sql?view=sql-server-ver16) somewhere, the only mention of order of operations is for `SET @variable = column = expression` – Charlieface Jun 26 '22 at 20:10
  • @Charlieface I've asked the doc team and the PMs to take a look at making the docs clearer. One comment I'll note is that there are a good number of "but you didn't say so explicitly in the docs" comments - some of them make more sense than others (this is a more complex area of the code, so I'll put it in the 'good to clarify' bucket). Thanks for your suggestion on how we can improve. I appreciate it. – Conor Cunningham MSFT Jun 27 '22 at 00:22

0 Answers0