TSQL: Common Table Expressions (CTEs)

Can be used in conjunction with data modification operators.
Similar to derived queries.
Not persisted beyond duration of executing statement.
Can be referenced multiple times in a single statement.
Can be used to handle recursive scenarios (parent-child hierarchy)
Allows to encapsulate data-source logic, reducing complexity of the actual data-modification statement.

Samples:

with ctelist as
               (select col1, col2 from table1 order by ID offset 50 rows fetch next 5 rows only)
update ctelist
set actualcost *=2, modifieddate = getdate();

(TOBECONTINUED)