Generating the Numbers using insert

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 2000 Forums

SQL Server Development (2000)

Generating the Numbers using insert

Author

Topic

svicky9
Posting Yak Master

232 Posts

Posted - 2006-06-29 : 12:25:40

Hi Friends

I have a table tab1 with 5 cols

col1 col2 col3 col4 col5
AB 09 45 3e
kj 10 15 4r
iu 44 16 5t
.
.
.
and so on till 110 rows

Now in the column 5 i want to generate numbers from 1-110
How can i do that

Col1 is a primary key

VIC

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-29 : 12:30:48

update tab1 set col5 = (select count(*) from tab1 t2 where t2.col1 <= tab1.col1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

svicky9
Posting Yak Master

232 Posts

Posted - 2006-06-29 : 12:45:24

Tnank you

Can you please explain me how this works
is it something to do with the primary key column

Vic

nr
SQLTeam MVY

12543 Posts

Posted - 2006-06-29 : 13:07:53

select count(*) from tab1 t2 where t2.col1 <= tab1.col1

counts all the rows on the table with a pk less than or equal to the pk on the current row and the update sets col5 to that value.This will be a consecutive numver in order of the pk as the pk must be unique.

another way is
declare @i int
select @i = 0
update tab1 set @i = col5 = @i + 1

but that won't be ordered.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-06-30 : 02:33:08

Where do you want to show data?
If you use Front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources