CODEWITHSUNDEEP
sql-servert-sql
scgough
scgough

Reputation: 5252

T-SQL - Updating a subset of records by Row_Number()

I have a table which has sub-sets of records for a parent table. Each subset has an order stored in a [rank] field. I need to update this field for a specific subset in this table based on a new order held in the field otherRank.

Does the following cover it:

update mytable t1 set
[rank] = t2.new_rank_num
from (select t2.id, new_rank_num = row_number() over (order by t2.otherRank)
    from mytable t2 where t2.parentID = 628) t2
where t1.id = t2.id

or, would I need:

update mytable t1 set
[rank] = t2.new_rank_num
from (select t2.id, new_rank_num = row_number() over (order by t2.otherRank)
    from mytable t2 where t2.parentID = 628) t2
where t1.id = t2.id and t1.parentID = 628

My specific issue is I don't want anything to updated outside of the remit of parentID 628

Edit I get an error when trying to run this:

incorrect syntax near t1 incorrect syntax near t2

so I'm thinking the syntax needs to be:

update mytable set
    [rank] = t2.new_rank_num
    from (select id, new_rank_num = row_number() over (order by otherRank)
from mytable where parentID = 628) t2
where id = t2.id and parentID = 628

Edit 2

OK, I've gone with a CTE Solution for this as recommended by SqlZim. It looks like this:

;with cte as (
  select t2.id, new_rank_num = row_number() over (order by t2.otherRank)
    from mytable t2 where t2.parentID = 628
)

update t1 set 
    [rank] = t2.new_rank_num
from mytable t1
inner join cte t2 on t1.id = t2.id

Upvotes: 0

Views: 254

Answers (2)

Mikhail Lobanov
Mikhail Lobanov

Reputation: 3026

You also can update views, not only tables.

Try this:

UPDATE T  
SET [rank] = [new_rank_num]
FROM (
    SELECT 
       [rank]         = [rank], 
       [new_rank_num] = row_number() over (order by otherRank)
    FROM mytable 
    WHERE parentID = 628 
) T

Upvotes: 0

SqlZim
SqlZim

Reputation: 38073

I prefer to do this sort of thing using a common table expression (cte):

;with cte as (
  select *
    , new_rank_num = row_number() over (
        partition by ParentId 
        order by otherRank
        )
  from mytable
)
update cte
set [rank] = new_rank_num
where ParentID = 628;

If you want to preview the changes before running the update, just change the above to a select instead of an update. Note that only the first statement after the cte can use the cte.

Upvotes: 4

Related Questions