Update -Serial Number from Row Number

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

General SQL Server Forums

New to SQL Server Programming

Update -Serial Number from Row Number

Author

Topic

paramu
Posting Yak Master

151 Posts

Posted - 2009-08-29 : 08:38:26

Iam using sql server 2008.
I want to update my table for a field 'srl_no' as its row number..Is it possible?

update my_prmst set srl_no=row_number() over (order by itm_code) where my_prno='2'
...........?

Thanks

Paramu @ PARANTHAMAN

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-08-29 : 11:09:55

[code]
update p
set srl_no = row_no
from
(
select srl_no, row_no = row_number() over (order by itm_code)
from my_prmst
where my_prno = '2'
) p
[/code]

KH
[spoiler]Time is always against us[/spoiler]

paramu
Posting Yak Master

151 Posts

Posted - 2009-08-30 : 02:01:42

ThankYou Khtan,

Nice Answer..!..!

Paramu @ PARANTHAMAN

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-31 : 02:38:40

quote:
Originally posted by paramu

Iam using sql server 2008.
I want to update my table for a field 'srl_no' as its row number..Is it possible?

update my_prmst set srl_no=row_number() over (order by itm_code) where my_prno='2'
...........?

Thanks

Paramu @ PARANTHAMAN

You should do this in your select statement. What happens if data changes?

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources