CODEWITHSUNDEEP
sqlsql-servert-sqlstored-proceduressql-update
Miquel Coll
Miquel Coll

Reputation: 771

SQL Update. Value is used and changed on the same statement. Always same behaviour?

I have encountered a problem with a procedure I am going to put into production. The thing is I'm going to make an update on a part of this procedure, f.i., as follows:

DECLARE @paramStartDate DATETIME = '2014-12-31 23:00'

UPDATE [table1]
SET    EndDate = Dateadd(hour, 23, StartDate),
       UpdateDate = Getdate(),
       StartDate = @paramStartDate
WHERE  StartDate < @paramStartDate

This update statement works as I expect but my question is: Does it matter where this procedure is executed? Is the update ALWAYS going to first assign the new EndDate and afterwards the StartDate? No environment settings, TimeZone, etc. that might affect it?

I tried to execute it in different servers but, as expected, I don't really have that many to check if it always will work as I expect.

Upvotes: 1

Views: 101

Answers (1)

William C.
William C.

Reputation: 416

In SQL Server, as @a-horse-with-no-name pointed out I am pretty sure any "standard" SQL db, the expressionS in the SET clause are logically executed at the same time. SQL Server would need to read first all the columns' values to use during the SET, and then proceed to physically update the columns. This view is supported by the following TechNet article: https://technet.microsoft.com/en-us/library/ms190623(v=sql.105).aspx. In the section "Processing Other Statements" you can read

The basic steps described for processing a SELECT statement apply to other SQL statements such as INSERT, UPDATE, and DELETE. UPDATE and DELETE statements both have to target the set of rows to be modified or deleted. The process of identifying these rows is the same process used to identify the source rows that contribute to the result set of a SELECT statement. The UPDATE and INSERT statements may both contain embedded SELECT statements that provide the data values to be updated or inserted.

Upvotes: 1

Related Questions