CODEWITHSUNDEEP
sqlsql-server-2008
RAS
RAS

Reputation: 8156

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

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.

Upvotes: 4

Views: 7009

Answers (3)

Janine Rawnsley
Janine Rawnsley

Reputation: 1258

I've assumed you'd want to group by user to generate the number and that item_order column is already there, just needs updating?

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

Upvotes: 1

Andomar
Andomar

Reputation: 238296

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.

Upvotes: 7

lc.
lc.

Reputation: 116538

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.

Upvotes: 3

Related Questions