Delete from a derived table in SQL Server?

Question

I am studying the query in the answer of the post IN operator in SQL Statement Does not work?, as below:

DELETE t
    FROM (SELECT t.*,
                 ROW_NUMBER(*) OVER (PARTITION BY P ORDER BY ABS((P & 0xFFFFFFFF) * 256 - F) as seqnum
          FROM MyTable t
         ) t
     WHERE seqnum > 1;

After many researchs, I finally figure out most of the issues, and revised the query as below:

DELETE t2
    FROM (SELECT t.*,
                 ROW_NUMBER() OVER (PARTITION BY P ORDER BY ABS((P & 0xFFFFFFFF) * 256 - F)) as seqnum
          FROM MyTable t
         ) t2
     WHERE seqnum > 1;

The changes are:

Add ")" after "F)", so that "OVER (PARTITION BY P ORDER BY ABS((P & 0xFFFFFFFF) * 256 - F))" follow the syntax at https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-ver15
Change ROW_NUMBER(*) to ROW_NUMBER() since the document above use ROW_NUBMER().
Change the t in the outer query to t2. The subquery in FROM clause must have an alias, so there must be an alias after the subquery. See (T-SQL) Why does this subquery need an alias? . Moreover, actual the alias in the outer query can be same as the one in the subquery, see https://dba.stackexchange.com/questions/16516/subqueries-aliases-same-as-main-queries-aliases, but they are refer to different objects. So I change the alias in the outer query from "t" to "t2"

It is a bit difficult for a novice to understand many knowledges in a complex query. Whatmore, I still have two things that cannot understand very well.

Why I must write "Delete t2 FROM" instead of "Delete FROM"? I only find this link Difference between DELETE and DELETE FROM in SQL? , but it is used for JOIN table, not for subquery.
From the link at https://dba.stackexchange.com/questions/120233/sql-server-delete-from-subquery-derived-table, I know the subquery is a derived table. Then the DELETE from t2 is just delete records from a dervied table(it is virtual), why it can also perform the same deletion operation on the underlying real table MyTable?

Thanks

Your primary source of confusion is the syntax of `DELETE`. You can write `DELETE FROM t2 FROM `. You can write the keyword `FROM` twice. The first `FROM` is optional. It much more readable to wrap subquery in CTE, as @Lukasz showed in his answer. — Vladimir Baranov, Feb 22 '20 at 09:44

Lukasz Szozda · Answer 1 · 2020-02-22T13:06:10.127

"Why I must write "Delete t2 FROM" instead of "Delete FROM"?

The easiest way is first to write a query that makes selection and then delete from it, for instance by using CTE:

WITH cte AS (
   SELECT * 
   FROM (
     SELECT t.*,
       ROW_NUMBER() OVER (PARTITION BY P ORDER BY ABS((P & 0xFFFFFFFF) * 256 - F)) as seqnum
     FROM MyTable t) sub
   WHERE seqnum > 1
)
SELECT * FROM cte; -- selecting
--DELETE FROM cte; -- deletion, no where condition, only the ones selected will be removed

Then the DELETE from t2 is just delete records from a dervied table(it is virtual), why it can also perform the same deletion operation on the underlying real table MyTable?

It is similar case to deleting from view. Rows can be deleted from derived table as long as SQL operations preserve key(no aggregations, DISTINCT, n:m relationship).

In this scenario adding window function introduces one new column, but it is always 1:1 to the existing data.

lptr · Accepted Answer · 2020-02-22T10:14:17.237

Why I must write "Delete t2 FROM" instead of "Delete FROM"?

It is because of the DELETE syntax

DELETE   
    [ FROM ]   <--optional keyword FROM
    { { table_alias  
      | <object>   <--> <object> ::= table_or_view_name
      | @table_variable 
    }
    [ FROM table_source [ ,...n ] ]  <-- note the whole FROM statement is optional, not only the FROM keyword
......

FROM table_source
Specifies an additional FROM clause. **This Transact-SQL extension to DELETE** allows specifying data from <table_source> and deleting the corresponding rows from the table in the first FROM clause.

DELETE (optional FROM) and could use either a table alias or an object which is a table or a view

There is no derived table/table_source after the DELETE, so (for deleting from a derived table) the only applicable option is to use an alias. The second FROM accepts a table_source, which could be a derived table. Hence, in a case of a derived table

DELETE [FROM] alias
FROM (....) as alias

why it can also perform the same deletion operation on the underlying real table MyTable

As long as the derived table keeps the granularity of each row (each row can be identified individually) and the scope of the dml is limited to a single table (taking into account the derived table definition) then dml operations on derived tables, views, ctes are possible.

Thank you very much. Your explanation is the best one! – alancc Feb 22 '20 at 23:01 — alancc, Feb 22 '20 at 23:01

Delete from a derived table in SQL Server?

2 Answers2