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:

1. 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
2. Change ROW_NUMBER(*) to ROW_NUMBER() since the document above use ROW_NUBMER().
3. 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.

1. 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.
2. 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

Answer 1

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.