Re-indexing table; update with from
- by David Thorisson
The query says it all, I can't find out the right syntax without without using a for..next
UPDATE Webtree SET Webtree.Sorting=w2.Sorting
FROM
(
SELECT
BranchID,
CASE
WHEN @Index>=ROW_NUMBER() OVER(ORDER BY Sorting ASC) THEN ROW_NUMBER() OVER(ORDER BY Sorting ASC)
ELSE ROW_NUMBER() OVER(ORDER BY Sorting ASC)+1
END AS Sorting
FROM Webtree w2
WHERE w2.ParentID=@ParentID
)
WHERE Webtree.BranchID=w2.BranchID
