sql server 2008 - How to increment value of a Column based on previous Row's value in SQL -

sql server 2008 - How to increment value of a Column based on previous Row's value in SQL -

i'm using sql server 2008.

i have 2 tables: user_master , item_master.

there user user_id = 10.

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

there 5 items item_id = 20 24.

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

there 1 more column in item_master item_order(int). want place item_order = 0 4 in these rows single query.

is possible?

edit :

item_id not supposed in order.

for example, instead of 20,21,22,23,24; 20,25,31,47,58.

you can utilize row_number() window function assign increasing number each row same user_id. subquery required because cannot utilize window functions straight in set clause.

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

live illustration @ sql fiddle.

sql sql-server-2008