SQL: Adding and populating an order column in an existing table

Found this post in my drafts folder. I don't remember doing this, but I might as well put it out there.

I'm sure I'm going the long way around the tree here, but recently I had to add an OrderId column to a table where I was going to implement ordering that hadn't been there before. In my brain I was sure I could just temporarily change the column to an identity column so that SQL could do the initial ordering for me. I'd then remove the identity from the column and allow my users to change the OrderId. SSIS didn't agree. Maybe it was the lack of sleep or caffeine. So, here's how I ended automating this process. If this was SQL 2005+, I'd be able to use some nifty functions, but in SQL 2000 they don't exist.

UPDATE [mytable] set orderid = rank
FROM (

SELECT t1.RecId, t1.CategoryId, t1.RecName, COUNT(*) AS rank
FROM [mytable] t1, [mytable] t2
where t1.CategoryId = t2.CategoryId and t1.RecName <= t2.RecName
GROUP BY t1.CategoryId, t1.RecId, t1.RecName

)
o where [mytable].RecId = o.RecId