SQL Server : does an CTE update work like a transaction?

Question

I use a query like this to update some columns of a table:

WITH MyTable AS
(
    SELECT TOP 1 
        *, 
        ROW_NUMBER() OVER (ORDER BY T.dtDate ASC) AS RowNum
    FROM 
        important.Table T
    WHERE 
        bWorking = 1
)
UPDATE MyTable
SET iIDfkToOtherTable = 6
WHERE RowNum = 1

There are two queries in this statement (SELECT and UPDATE), so I'm not sure if another user will be able to change the "important.Table" values while I am between the SELECT and the UPDATE. Can someone give me a clue? Thanks!

user_84206 · Answer

It is essentially a single query for the sake of atomicity. It would be essentially the same as UPDATE ... FROM in those terms:

https://stackoverflow.com/a/2334741/84206

In fact here's a similar but different question that suggests such a CTE for accomplishing atomicity in lieu of a two step select/update:

https://stackoverflow.com/a/42470136/84206

user_8898324 · Answer

Don't think of it as two queries. That's procedural thinking. SQL Server is set based, and in this case you are defining an end state "This set of data will have this transformation applied".

As a single definition it will always be atomic.

SQL Server : does an CTE update work like a transaction?

2 Answers