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

Transact-SQL (2000)

added counter

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-15 : 09:11:25

i have 100 rows in TABLE1 like that (id,NAME,PASS)

0,aa,bb
0,ab,dd
.......
0,cc,ff

i want that will be:
1,aa,bb
2,ab,dd
....
100,cc,ff

how can i do it?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-15 : 10:34:02

[code]update t set id = rownum
from (select id, row_number() over (order by name, pass) as rownum from Table1) t[/code]

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-15 : 12:26:57

sorry, i used in sql 2000

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-02-15 : 13:29:52

[code]update ta
set ta.id = (select count(*) from Table1 tb where tb.name + tb.pass <= ta.name + ta. pass)
from Table1 ta[/code]However, this will not work quite well if you have duplicate entries in the table - will skip one number and then assign same id to the identical rows.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-15 : 14:49:32

Why don't you change id column to IDENTITY(1,1).?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-02-15 : 15:05:43

Moving this to the 2000 forum.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-16 : 01:09:38

sodeep,
because i have other rows that dont nedd this counter

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-16 : 01:18:48

just try like this


declare @tab table(id int,NAME varchar(32),PASS varchar(32))
insert into @tab select 0,'aa','bb' union all 
select 0,'ad','dd' union all
select 0,'cc','ff'

select * from @tab

update t
set t.id = (select count(*) from @tab where PASS  <= t.PASS )
from @tab t

select * from @tab

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 09:25:10

quote:

Originally posted by bklr

just try like this


declare @tab table(id int,NAME varchar(32),PASS varchar(32))
insert into @tab select 0,'aa','bb' union all 
select 0,'ad','dd' union all
select 0,'cc','ff'

select * from @tab

update t
set t.id = (select count(*) from @tab where PASS  <= t.PASS )
from @tab t

select * from @tab

Won't work if duplicate...

Use:

Select Identity(int,1,1)as rownum,* into #temp from @tab

and then update your table based on rownum.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-16 : 12:53:48

i try it with fetch,why it put 1 in each row?

here the code:

declare @Count numeric

declare csrNameSet as cursor for
select CustomerOrderNumber from TABLE1

select @count=1

open csrNameSet

while @@FETCH_STATUS =0
begin
	fetch next from csrNameSet
	update TABLE1 set CustomerOrderNumber=@count
	where TABLE1.id='0'
	select @count=@count+1
end

close csrNameSet
deallocate csrNameSet
GO

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-16 : 12:55:09

quote:

Originally posted by inbs

i try it with fetch,why it put 1 in each row?

here the code:

declare @Count numeric

declare csrNameSet as cursor for
select CustomerOrderNumber from TABLE1

select @count=1

open csrNameSet

while @@FETCH_STATUS =0
begin
	fetch next from csrNameSet
	update TABLE1 set CustomerOrderNumber=@count
	where TABLE1.id='0'
	select @count=@count+1
end

close csrNameSet
deallocate csrNameSet
GO

Why Cursor? Did you try my approach?

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-16 : 12:58:53

if i have this rows for example it is not work,yes?

0,aa,gg
0,ab,zzzff
.......
0,cc,a

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-17 : 00:48:35

quote:
Originally posted by inbs

if i have this rows for example it is not work,yes?

0,aa,gg
0,ab,zzzff
.......
0,cc,a

do u have any identity column in ur table
i think no need of cursors for this.
use sodeep code, change my code as sodeep sugessted.........

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-17 : 01:16:59

i can't use it becasue if i have this rows:
0,'aa','sds'
0,'ad','aa'
0,'cc','aa'

the ruslt is(ti is wrong cause i get 2 twice):
3 aa sds
2 ad aa
2 cc aa

sridhar.dbe
Starting Member

34 Posts

Posted - 2009-02-17 : 01:26:37

hi,
using cursors, try this.
declare @tab table(id int,NAME varchar(32),PASS varchar(32))
insert into @tab select 0,'aa','bb' union all
select 0,'ab','dd' union all
select 0,'ad','dd' union all
select 0,'cc','ff' union all
select 0,'aa','gg' union all
select 0,'ab','zzzff'

declare @Count numeric
declare @name varchar(32),@PASS varchar(32)
declare csrNameSet cursor for select name,pass from @tab

select @count=1

open csrNameSet
fetch next from csrNameSet into @name,@pass
while @@FETCH_STATUS =0
begin
update t set id=@count
from @tab t
where t.id='0'
and t.name=@name
and t.pass=@pass
fetch next from csrNameSet into @name,@pass
select @count=@count+1
end
close csrNameSet
deallocate csrNameSet

select * from @tab

But Dont use cursors when there is no need ,as said by peso and bklr.

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-02-17 : 01:29:46

2 questions?

1.why Dont use cursors ,performance?
2.why do you use with valuse @name,@pass, if i have 20 columns, i have to write them?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-17 : 08:30:19

quote:
Originally posted by inbs

i can't use it becasue if i have this rows:
0,'aa','sds'
0,'ad','aa'
0,'cc','aa'

the ruslt is(ti is wrong cause i get 2 twice):
3 aa sds
2 ad aa
2 cc aa

No way. See and use my approach posted in 02/16/2009 : 09:25:10. Donot use cursors.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources