I wanted to update the ranks of my table Performance
and I found this solution, but I do not understand why this SQL code works:
WITH cte AS (
SELECT AllTimeRank, r = RANK () OVER (PARTITION BY Distance ORDER BY TimeInSeconds)
FROM dbo.Performance
)
UPDATE cte
SET AllTimeRank = r FROM cte;
The statement says UPDATE CTE
but in fact, it really updates the table (dbo.Performance
).
Can someone explain me this magic?
The following statement seems to do exactly the same:
WITH cte AS (
SELECT Id, r = RANK () OVER (PARTITION BY Distance ORDER BY TimeInSeconds)
FROM dbo.Performance
)
UPDATE dbo.Performance
SET AllTimeRank = r
FROM cte INNER JOIN dbo.Performance p ON p.Id = cte.Id;
Best Answer
No magic. CTE in this case is an updatable view.
Updatable and Insertable Views:
In general - no. Simply imagine that
id
is not unique.Two queries with different (general) logic cannot be compared. - akina