How to increment value of a Column based on previous Row's value in SQL

Question

I'm using SQL Server 2008.

I have two Tables: User_master and Item_master.

There is a User with user_id = 10.

|---------|
| user_id |
|---------|
|   10    |
|---------|

There are 5 Items with item_id = 20 to 24.

|---------|---------|------------|
| item_id | user_id | item_order |
|---------|---------|------------|
|   20    |    10   |     0      |
|---------|---------|------------|
|   21    |    10   |     0      |
|---------|---------|------------|
|   22    |    10   |     0      |
|---------|---------|------------|
|   23    |    10   |     0      |
|---------|---------|------------|
|   24    |    10   |     0      |
|---------|---------|------------|

There is one more column in Item_master that is item_order(int). I want to place item_order = 0 to 4 in all these rows with only single query.

Is it possible?

EDIT :

item_id is not supposed to be in order.

For example, instead of 20,21,22,23,24; it could be 20,25,31,47,58.

Answer 1

You can use the row_number() window function to assign an increasing number to each row with the same user_id. A subquery is required because you cannot use window functions directly in the set clause.

update  im
set     im.item_order = im.rn
from    (
        select  row_number() over (partition by user_id
                                   order by item_id) - 1 as rn
        ,       *
        from    item_master
        ) as im;

Live example at SQL Fiddle.

Answer 2

Extrapolating a little bit and since {item_id, user_id} is unique in the table, here is a generic solution:

UPDATE m
SET item_order = x.new_item_order
FROM item_master m
INNER JOIN (
  SELECT [item_id], [user_id], 
      (ROW_NUMBER() OVER (PARTITION BY [user_id] 
                          ORDER BY [item_id]))-1 AS [new_item_order] 
  FROM item_master
) x ON m.item_id = x.item_id AND m.user_id = x.user_id

SQL Fiddle example

This will set the item_order column in order of item_id for each user, starting at 0.