UPDATE table using CTE 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;
No magic. CTE in this case is an updatable view.

Updatable and Insertable Views:


SQL Server
Oracle
MySQL
DB2
Teradata



  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