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 <some subquery>. 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
– Vladimir Baranov, Commented Feb 22, 2020 at 9:44

lptr · Accepted Answer · 2020-02-22 10:14:17Z

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.

Lukasz Szozda · Accepted Answer · 2020-02-22 13:06:10Z

"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.

Collectives™ on Stack Overflow

Delete from a derived table in SQL Server?

2 Answers 2

1 Comment

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Linked

Related