@Dick: VERY IMPORTANT!!!
Based on your previous note "4. No clustered index yet " Do NOT run the code until you added a clustered index that supports the order the data need to be updated!!
I'd probably go with
CREATE UNIQUE CLUSTERED INDEX CX_testtable_ImportantForQuirkyUpdate
ON testtable (Category,Class,Location,RowNo)
Regarding performance:
I duplicated your data to build a Million-Row table (increasing Category value, actually 1.4Mill rows). Waynes code run in 3 seconds (excluding the final SELECT * which took another 17 sec). My system: Home PC with WinXP and SQL2K5.
Range as expected 😀