Increment by 1 - 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

General SQL Server Forums

New to SQL Server Programming

Increment by 1

Author

Topic

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 07:56:27

Hi! I am having some challenges in the table I am working on. I have 3 columns (s_num, s_entity, s_name) and the data in the last 2 columns I have copied in a different table. So the challenge is I have to number the first column which is s_num. I have tried using the following code but did not work.

DECLARE @count int
set @count = 1
while @count < 247
begin
update entity
set s_num = @count
set @count=@count+1
end

The above code only gives me the total count of rows, what i need is the rows to be numbered, incrementing by 1.

I have 246 rows and I need the s_num column to be numbered from 1...246.

Thanks in advance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 07:58:09

just make s_num column as a identity column and it will autonumber itself as on insertion of values to other fields.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 07:58:59

alternatively if you need to manually number it sequentially, have a look at ROW_NUMBER() function

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 08:02:10

Hi visakh! I am really new in sql and all i have to do is number all rows to reflect in the s_num column. Any idea on what code should i execute? Am I on the right track in the code I posted here? :(

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 08:04:50

Some are saying I have to use cursor. Need help on how to execute code using cursor to increment rows by 1 and finally reflect the numbers in my s_num column

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 08:04:53

have you already inserted or are you about to insert?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 08:06:58

the row insertion will happen in a different program. I just have to number the rows in my database. currently I have 246 rows and I have to number it from 1...246. :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 08:09:25

ok then it should be


UPDATE t
SET s_num=rn
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s_name) AS rn,s_num
      FROM entity)t

I'm assuming you've no control over table schema otherwise by all means alter table to make s_num an IDENTITY column

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 08:23:16

Hi again! I have tried to use the query but it gives me an error -
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'from'.

:(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 08:27:58

post used code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 08:31:02

oops. late posting of the code :D
here it is:

update entity
set s_num = rn
from (select ROW_NUMBER() over (order by s_entity_name)) as rn, s_num
from entity)

thank you so much for helping :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 08:34:31

quote:
Originally posted by freshman11

oops. late posting of the code :D
here it is:

update t
set s_num = rn
from (select ROW_NUMBER() over (order by s_entity_name)) as rn, s_num
from entity)t

thank you so much for helping :D

please try to use exact posted code. your above code is different
see edited one

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-20 : 08:35:11

quote:
Originally posted by freshman11

oops. late posting of the code :D
here it is:

update ~~entity~~E
set s_num = rn
from (select ROW_NUMBER() over (order by s_entity_name)) as rn, s_num
from entity) E

thank you so much for helping :D

Corey

I Has Returned!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 08:37:46

quote:
Originally posted by Seventhnight

quote:
Originally posted by freshman11

oops. late posting of the code :D
here it is:

update ~~entity~~E
set s_num = rn
from (select ROW_NUMBER() over (order by s_entity_name)) as rn, s_num
from entity) E

thank you so much for helping :D

Corey

I Has Returned!!

It still has an additional closing braces

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 08:45:19

Hi again! I have used the below code:
UPDATE entity
SET s_num=rn
FROM (SELECT ROW_NUMBER() OVER (ORDER BY s_entity_name) AS rn,s_num
FROM entity)

and now the error is -> Msg 102, Level 15, State 1, Line 4
Incorrect syntax near ')'. :(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 08:46:41

Can you please copy paste my suggestion and use it? For Gods sake dont make any amendments

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

freshman11
Starting Member

13 Posts

Posted - 2011-07-20 : 08:58:52

Got it! Thanks so much! :D

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-07-20 : 09:03:10

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources