gistfile1.sql

declare @meal_scale_id int
set @meal_scale_id = 5041
;with order_seq_num(meal_scale_id,recipe_scale_id,sort_value)
as (
	Select ms.meal_scale_id, ms.recipe_scale_id, sort_value = row_number() over(partition by meal_scale_id order by sort_value) -1
	from ed_diet_meal_scale_recipe_scale_xref ms with(nolock) 
	--where ms.meal_scale_id in (5389,5391,5393,5041)
	
)

update msrsx 
set msrsx.sort_value = t.sort_value
from order_seq_num t
inner join ed_diet_meal_scale_recipe_scale_xref msrsx on msrsx.meal_scale_id = t.meal_scale_id and msrsx.recipe_scale_id = t.recipe_scale_id