I have the following delete statement, which I essentially grabbed from the internet:
delete a
from (
select *,
Row_Number() over (partition by mpi, beg_dt, end_dt order by data_id desc) as rn
from clms.groups
) as a
where a.rn > 1
This deleted the required records from clms.groups. I was a bit surprised this actually worked, since the from clause doesn't refer to a table, but a query. How does sql know what to do. What if there had been a join in the subquery?
I think in the end sql first execute your subquery which produces for you a bunch of rows from clms.groups which "create the new table" named "a" for your delete request. Because this rows match still your subquery table ( not like with a join) it is not a big problem. Join statements are from my experience often not supported for delete querys, but there are other commands to get infos from different tables for your delete statement, e.g. "USING" for postgresql.
I hope that helps a bit.
aandais a derived table with a single base-table; this is the same as an updatable view and works because SQL Server knows there's a single table being referenced.