Update every select row result without cursor

Question

On my way learning to do different things with tsql. I want to improve my code and would like to get an advice.

Let's say I have this select:

SELECT [Id], [Type]
FROM 
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [Id] ORDER BY CurrentDate DESC) AS RowN,
        CS.*
    FROM [CookieStore] CS 
)RN
WHERE RN.RowN = 1

Which returns me result table with couple of rows:

1) id=5  Type='chocolate'
2) id=6  Type='oatmeal'
3) id=7  Type='gingerbread'

To achieve my goal I do cursor over select result to make update:

/* some cursor logic */

UPDATE [SweetShop] SET [Type] = @CookieShopType WHERE [Id]=@CookieStoreId

/* some cursor logic */

Is there any way to make update for every row from select without cursor? Or in this case there are no other options?

Answer 1

You can perform this operstion with a set-based approach (without cursors).

First you define a CTE using your original query, then you can use an UPDATE FROM statement to update the target table from a join between the CTE and the target table:

;with temp as (
    SELECT [Id], [Type]
    FROM 
    (
        SELECT
            ROW_NUMBER() OVER(PARTITION BY [Id] ORDER BY CurrentDate DESC) AS RowN,
            CS.*
        FROM [CookieStore] CS 
    )RN
    WHERE RN.RowN = 1
)
update 
    [SweetShop]
set 
    [Type] = t.[Type]
from
    [SweetShop] as d 
        inner join
    temp as t
        on t.Id = d.Id