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!

Answer 1

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