vaxlt vaxlt - 1 year ago 86
SQL Question

Update SQL table to create order number

I recently updated SQL table to include order number to the rows. Now I have to write SQL statement to update rows with correct order values. It should look like this:

Id, UserName, Store, OrderNumber
1, User1, store1, 1
2, User2, store1, 2
3, User3, store1, 3
4, User4, store2, 1
5, User5, store2, 2

At the moment column OrderNumber contains only zero's.

How I should proceed?

Answer Source

You can do this with ROW_NUMBER

With CTE as
select row_number() Over(partition by Store order by Id) as O_number,*
From yourtable
Update CTE 
SET OrderNumber = O_number