How to increase column value based on previous row value in SQL

Question

I am using SQL Server 2008.

I have two tables: User_master and Item_master .

User has 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 | |---------|---------|------------| 

Item_master has one more column item_order (int) . I want to place item_order = 0 to 4 on all of these lines with just one request.

Is it possible?

EDIT:

item_id should not be in order.

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

Answer 1

You can use the window function row_number() 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 in SQL Fiddle.

Answer 2

By extrapolating a little, and since { item_id , user_id } is unique in the table, here is a general 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 item_order order for each user, starting at 0.

Answer 3

I assumed that you want the group to generate a number, and this item_order column already exists, just need an update?

 update IM set item_order = t.RowNumber FROM Item_master IM INNER JOIN (select item_id , user_id , ROW_NUMBER() over(PARTITION BY user_id order by item_id ) -1 as 'RowNumber' from Item_master) T ON T.item_id = IM.item_id