0

I am using SQL Server 2012 and higher. For an quirky update SQL server executes as following

DECLARE @Variable1, @Variable2, @Variable3

UPDATE A
SET
A.COLUMN1 = @Variable1, -- Step 4
A.COLUMN2 = @Variable3, -- Step 5
.
.
.
@Variable1 = IIF(@Variable2 = 0, A.COLUMN3, A.COLUMN2), -- Step 1
@Variable2 = IIF((@Variable3 IS NULL OR A.COLUMN3 = @Variable3), 1, 0), -- Step 2
@Variable3 = A.COLUMN1, -- Step 3
.
.
.
FROM TABLE_NAME A

Is there any way to change the order of execution steps by force ??

Esty
  • 1,882
  • 3
  • 17
  • 36

1 Answers1

3

Within an UPDATE statement, all assignments within the SET clause are executed as if they're all performed in parallel. As such, even your supposed execution order is incorrect. All occurrences of columns on the right hand side of the assignments will always reflect the value held in that column from before the UPDATE started. This leads to nice tricks such as:

UPDATE tab SET a = b, b = a

Which swaps the content of the a and b columns.

If you could edit your question to add more context, such as the existing contents of the table and what you're trying to achieve, I may be able to edit my answer and propose a concrete solution to the problem.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • Would u please explain this [link](http://stackoverflow.com/questions/31980396/update-sequence-in-sql-server) – Esty Oct 01 '15 at 07:22
  • 2
    @TanjimRahman - I'd be wary about that particular answer. What I've stated in my answer is standard SQL and SQL Server does follow that, so far as column assignments are concerned. However, assignments to *variables* within the `SET` clause is a Microsoft extension. I'm not aware that they have offered any guarantees on whether variable assignments will *always* happen before column assignments or if it just happens to work in the current circumstance – Damien_The_Unbeliever Oct 01 '15 at 07:30
  • thnx for ur explanation. I know that variables in SET clause is risky because there is no guarantee provided by Microsoft. That's why I want to know that is there any way to force that variable assignment will always happen before column assignment and also to force their execution serial. – Esty Oct 01 '15 at 07:58