subquery you have in your code is called a derived table. It's not a base table but a table that "lives" during the time that the query runs. Like views (which are also called viewed tables) - and in recent versions CTEs which is another, 4th way to "define" a table inside a query - they are similar to a table in many ways. You can
select from them, you can use them in
from or to
join them to other tables (base or not!).
In some DBMS, (not all DBMS have implemented this the same way) these tables/views are updatable. And "updatable" means that we can also
insert into or
delete from them.
There are restrictions though and this is expected. Imagine if the
subquery was a join of 2 (or 17 tables). What would
delete mean then? (from which tables should rows be deleted?) Updatable views is a very complicated matter. There's a recent (2012) book, entirely on this subject, written by Chris Date, well known expert in relational theory: View Updating and Relational Theory.
When the derived table (or view) is a very simple query, like it has only one base table (possibly restricted by a
WHERE) and no
GROUP BY, then every row of the derived table corresponds to one row in the underlying base table, so it is easy* to update, insert or delete from this.
When the code inside the subquery is more complex, it depends on whether the rows of the derived table/view can be traced/resolved to rows from one of the underlying base tables.
For SQL Server, you can read more in the Updatable Views paragraph in MSDN:
You can modify the data of an underlying base table through a view, as
long as the following conditions are true:
Any modifications, including
DELETE statements, must reference columns from only one base table.
The columns being modified in the view must directly reference the underlying data in the table columns. The columns cannot be derived in
any other way, such as through the following:
An aggregate function:
A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using
the set operators
amount to a computation and are also not updatable.
The columns being modified are not affected by
TOP is not used anywhere in the select_statement of the view together with the
WITH CHECK OPTION clause.
The previous restrictions apply to any subqueries in the
of the view, just as they apply to the view itself. Generally, the
Database Engine must be able to unambiguously trace modifications from
the view definition to one base table.
delete is easier, less complex than
update. SQL Server only needs the primary keys or some other way to identify which rows of the base table are to be deleted. For
update, there is an additional (rather obvious) restriction that we can't update a computed column. You can try to modify your query to do an update. Updating the
CreatedDateTime will probably work just fine but trying to update the computed
RowNumber column will raise an error. And
insert is even more complex, as we'd have to provide values for all the columns of the base table that don't have a