Autoincrement ID - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2012 Forums

Transact-SQL (2012)

Autoincrement ID

Author

Topic

sudhirbharti
Starting Member

16 Posts

Posted - 2013-12-10 : 23:43:21

I want to update id column to be reset and incremented by 1 whenever the new siteid will come;

Raw Table;

ID SITEID PARSINGTYPE CONTROLLING SENSORID ALIAS ID2
0 95 3 0 1 Delivery Left 1
0 95 3 0 3 Delivery Right 2
0 95 4 0 4 Return Left 3
0 95 4 0 2 Return center 4
0 95 4 0 5 Return right 5
0 96 3 0 1 Delivery Left 6
0 96 3 0 2 Delivery Right 7
0 96 4 0 3 Return L 8
0 96 4 0 4 Return Center 9
0 96 4 0 5 Return R 10

Desired table

ID SITEID PARSINGTYPE CONTROLLING SENSORID ALIAS ID2
1 95 3 0 1 Delivery Left 1
2 95 3 0 2 Delivery Right 2
3 95 4 0 3 Return Left 3
4 95 4 0 4 Return center 4
5 95 4 0 5 Return right 5
1 96 3 0 1 Delivery Left 6
2 96 3 0 2 Delivery Right 7
3 96 4 0 3 Return L 8
4 96 4 0 4 Return Center 9
5 96 4 0 5 Return R 10

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-12-11 : 09:04:01

[code];with cte as
( select id, row_number() over (partition by siteid orderby id2) as RN
from YourTable
) update cte set id = RN;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources