T-SQL Update Statment with ORDER BY

Question

I am using SQL Server 2005. I would like to update fields like Order BY MatchId orders as below query. But updeted fields not order by MatchId.

DECLARE @counter int

SET @counter = 10008

UPDATE Matches 
SET @counter = MatchNumberCounter = @counter + 1
WHERE MatchId IN 
      (SELECT TOP (232) MatchId FROM Matches WHERE LeagueStatueId = 280 AND Week <> 1 
       ORDER BY Week, MatchDate, MatchTime)

Thanks already now

Answer 1

It looks like you are trying to do a "quirky update". This is an undocumented technique and there are lots of conditions that need to be met for this to work. These are discussed here (requires free registration)

You should be able to do something like this though.

;With cte As
(
SELECT TOP (232) MatchId ,
                 MatchNumberCounter, 
                 ROW_NUMBER() over (order by MatchId) as RN
FROM Matches 
WHERE LeagueStatueId = 280 AND Week <> 1 
ORDER BY Week, MatchDate, MatchTime
)
UPDATE cte SET MatchNumberCounter=RN+10008