T-sql – UPDATE table using CTE

ctet-sqlupdate

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:

The following statement seems to do exactly the same:

In general - no. Simply imagine that id is not unique.

Is the first option better?

Two queries with different (general) logic cannot be compared. - akina