The 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 update
, 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: CREATE VIEW
.
Updatable Views
You can modify the data of an underlying base table through a view, as
long as the following conditions are true:
Any modifications, including UPDATE
, INSERT
, and 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: AVG
, COUNT
, SUM
, MIN
, MAX
, GROUPING
, STDEV
, STDEVP
, VAR
, and VARP
.
A computation. The column cannot be computed from an expression that uses other columns. Columns that are formed by using
the set operators UNION
, UNION ALL
, CROSSJOIN
, EXCEPT
, and INTERSECT
amount to a computation and are also not updatable.
The columns being modified are not affected by GROUP BY
, HAVING
, or DISTINCT
clauses.
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 FROM
clause
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.
Actually 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 DEFAULT
constraint.