I've added an integer row [SortNo] to an existing SQL table. Now I want to fill the new integer column [SortNo] with integers rising by 10 (10, 20, 30, ...), BUT I want the rows to be ordered by another nvarchar-column [Name].
I can't just use Update, since it would set the [SortNo] in a random order, not ordered by [Name].
So, how can I fill [SortNo] ordered by [Name]?
You can do this using an updatable CTE and window functions:
with toupdate as ( select t.*, row_number() over (order by [Name]) as seqnum from t ) update toupdate set sortno = 10 * seqnum;