Re-indexing table; update with from

Question

The query says it all, I can't find out the right syntax without without using a WHILE loop

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

Answer 1

Not sure what version of SQL Server you're on - if you're on SQL Server 2005 or up, you could try and use a CTE (Common Table Expression):

; WITH BaseData AS
(
   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
   WHERE ParentID = @ParentID
)
UPDATE Webtree 
SET Webtree.Sorting = base.Sorting
FROM BaseData base
WHERE Webtree.BranchID = base.BranchID

Not sure where those @Index and @ParentID values come from - they would have to be available to the CTE, of course!

The CTE creates something like an "inline temporary view" which is available to just the next statement after it. This way, you can often "disentangle" two intermixed statements and make your intent and your steps clearer (to someone else reading the code, or to yourself if you have to come back and change this in 6 months time)...