count is not incrementing - 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

count is not incrementing

Author

Topic

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-01 : 05:57:43

hi i have one temp table like this
CREATE TABLE #temp
(eqno int,
counter int

)
delete from #temp
Insert into #temp values(50001580,1)
Insert into #temp values(50001580,1)
Insert into #temp values(50001581,1)
Insert into #temp values(50001581,1)
Insert into #temp values(50001582,1)
Insert into #temp values(50001582,1)

select * from #temp

declare @v_rowcount int
declare @i int
set @i=0
set @v_rowcount= (select count(*) from #temp)
--print @v_rowcount
WHILE (@v_rowcount<>0)
BEGIN
print @v_rowcount
print '------------'
--print @i

-- insert into #temp (counter)values(@i)
-- update #temp
-- SET counter = @i
set @i=@i+1
update #temp
SET counter = @i
print @i
set @v_rowcount=@v_rowcount-1
END
here table is not getting updated even i checked by printing he values of variables those are flowing correctly is there any prob with update statement?

and later on i need to reset this counter per equipment wise
to 1
like
50001580,1
50001580,2
50001581,1-- reset when eqno changes
50001581,2
50001582,1-- reset when eqno changes
50001582,2

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-01 : 07:11:56

try like this without using while
update tt
set counter = t.rid
from #temp tt
inner join (select row_number()over(partition by eqno order by eqno)as rid , eqno from #temp) t on t.eqno = tt.eqno

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-01 : 11:22:46

quote:
Originally posted by bklr

try like this without using while
update tt
set counter = t.rid
from #temp tt
inner join (select row_number()over(partition by eqno order by eqno)as rid , eqno from #temp) t on t.eqno = tt.eqno

No need of join:

Update t
set t.counter = t.rid
from  
(select row_number()over(partition by eqno order by eqno)as rid , * from #temp) 
t

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-01 : 11:41:09

In SQL 2000:

--Create table variable
Declare @t table
(eqno int,counter int) 

--Insert sample data
Insert into @t values(50001580,1)
Insert into @t values(50001580,1)
Insert into @t values(50001581,1)
Insert into @t values(50001581,1)
Insert into @t values(50001582,1)
Insert into @t values(50001582,1)

--Make Unique
Select IDENTITY(int,1,1) as ID,* into #K 
from @t

--Roll the Wheel
Update Z
Set Z.Counter = Z.ROW_ID
from(
select eqno,Counter,(Select Count(*) from #K Where eqno = t.eqno and ID <= t.ID)as ROW_ID
from #K t)Z

--Desired output
Select Eqno,Counter from #K

--output
Eqno      Counter
50001580	1
50001580	2
50001581	1
50001581	2
50001582	1
50001582	2

qutesanju
Posting Yak Master

193 Posts

Posted - 2009-02-02 : 04:57:10

it's giving error --in sql server 2000
Derived table 'Z' is not updatable because a column of the derived table is derived or constant.

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-02-02 : 06:11:08

Declare @t table
(eqno int,counter int)

--Insert sample data
Insert into @t values(50001580,1)
Insert into @t values(50001580,1)
Insert into @t values(50001581,1)
Insert into @t values(50001581,1)
Insert into @t values(50001581,1)
Insert into @t values(50001582,1)

--Make Unique
Select IDENTITY(int,1,1) as ID,* into #K
from @t

--Roll the Wheel
Update #k
Set Counter = Z.ROW_ID
from(
select ID as ID1,eqno,Counter,(Select Count(*) from #K Where eqno = t.eqno and ID <= t.ID)as ROW_ID
from #K t)Z
WHERE ID = z.ID1

--Desired output
Select Eqno,Counter from #k

drop table #K

Rahul Shinde

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-02-02 : 09:18:54

[code]Update m
Set m.Counter = Z.ROW_ID
from #K m
inner join(
select eqno,Counter,(Select Count(*) from #K Where eqno = t.eqno and ID <= t.ID)as ROW_ID
from #K t)Z
on m.eqno = Z.eqno

Select eqno,Counter from #K[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources