1

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;
0

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

  • @akina, in my case then, which approach do you suggest? I need to update the ranks of my table, as illustrated in my question. – Matteo Gariglio Jun 12 at 12:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.