Update table rows with incremental number within group

Question

I need to update a table so that rows of the same 'group' (Code + Color in the example) get an incremental number within the group. Rows for each group should be numbered 1, 2, 3...

id   | Code | Color   |Required_New_field
--------------------------
1231 |001   |Red      | 1
1232 |001   |Red      | 2
1233 |002   |Red      | 1
1234 |001   |Red      | 3
1235 |001   |Blue     | 1
1236 |002   |Red      | 2
1237 |001   |Blue     | 2
1238 |002   |Blue     | 1
1239 |002   |Red      | 3
...

In the example rows with Code=001 and Color=Red should get 1, 2, 3 respectivelly.

I've tried several approaches using subqueries and 'group by', but I realize is not actually the right way.

Any hint will be apprecciated!

Edited: ROW_NUMBER() answers are great! Sadly I have to run it on an old sql_server 2000 version. And ROW_NUMBER() is available on 2005 and above (details on availability here). Any alternatives?

Answer 1

While I do agree with everyone from comments that ROW_NUMBER() was available in SQL Server 2005, here is alternative solution using a sub-query to COUNT(). Can be used on SQL Server 2000. Mind that, performance-wise, it's much more costly:

SELECT 
    t2.*,
    (SELECT COUNT(*) 
     FROM your_table t1 
     WHERE t1.code = t2.code 
       AND t1.color = t2.color
       AND t1.id <= t2.id) AS Rn
FROM 
    your_table t2

EDIT - For update:

UPDATE t2 
SET RN = (SELECT COUNT(*) 
          FROM your_table t1 
          WHERE t1.code = t2.code 
            AND t1.color = t2.color
            AND t1.id <= t2.id) 
FROM your_table t2