CODEWITHSUNDEEP
sqlsql-server-2008
Vax
Vax

Reputation: 353

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?

Upvotes: 1

Views: 66

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93744

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

Upvotes: 1

Related Questions