gistfile1.txt

WITH sortOrderFixer AS
(
SELECT [YourId], ROW_NUMBER() OVER(ORDER BY [NameColumn] asc) AS RowNum
from [YourTable]
where active = 'yes'
)

update [YourTable] set SortOrder = sortOrderFixer.RowNum
from [YourTable] t join
sortOrderFixer on sortOrderFixer.[YourId] = t.[YourId]