Will using and updating the same field in one UPDATE statment cause undefined behaviour?

Question

Here is an example

UPDATE duration = datediff(ss, statustime, getdate()), statustime = getdate() where id = 2009

Is the field duration going to be assigned an undefined assigned value since statustime is getting used and assigned in the same statement? (i.e. positive value if datediff processed first or negative if statustime is processed first)

I can definitely update it in two separate statements but I am curious it is possible to update it in one statement.

Answer 1

My gut instinct says 'no', but this will vary depending on the SQL implementation, query parser, and so on. Your best bet in situations like these is to run a quick experiment on your server (wrap it in a transaction to keep it from modifying data), and see how your particular implementation behaves.

Answer 2

No. Both values are calculated before either assignment is made.

Update:
I tracked down the ANSI-92 spec, and section 13.10 on the UPDATE statement says this:

The <value expression>s are effectively evaluated for each row of T before updating any row of T.

The only other applicable rules refer to secion 9.2, but that only deals with one assignment in isolation.

There is some room for ambiguity here: it could calculate and update all statustime rows first and all duration rows afterward and still technically follow the spec, but that would be a very ... odd ... way to implement it.