Can anyone figure out why this WHILE loop never ends. drop table #temp1 drop table #temp3 create table #temp1(title varchar(50),const int,timestamp datetime) CREATE TABLE #TEMP3(TITLE VARCHAR(50),const INT,TIMESTAMP DATETIME) insert into #temp1 values ('MCCainCampaigns1',-101,'21-Aug-2008') insert into #temp1 values ('MCCainCampaigns2',-101,'21-Aug-2008') insert into #temp1 values ('MCCainCampaigns3',-101,'21-Aug-2008') insert into #temp1 values ('MCCainCampaigns4',-101,'21-Aug-2008') declare @tempstrconst varchar(50) set @tempstrconst=(select top 1 title from #temp1) while @@rowcount<>0 begin update #temp1 set intconst=intconst-1 where title=@tempstrconst insert into #temp3 select * from #temp1 where title='MysterySeries_Vol1' delete from #temp1 where title='MysterySeries_Vol1' set @tempstrconst=(select top 1 title from #temp1) end --Desired output: select * from #temp3 'MCCainCampaigns1',-102,'21-Aug-2008' 'MCCainCampaigns2',-103,'21-Aug-2008' 'MCCainCampaigns3',-104,'21-Aug-2008' 'MCCainCampaigns4',-105,'21-Aug-2008'
Hi, WHILE loop is running infinite because of SET condition "set @tempstrconst=(select top 1 title from #temp1)". @@ROWCOUNT Returns the number of rows affected by the last statement and after executing "set @tempstrconst=(select top 1 title from #temp1) ", @@ROWCOUT changes to 1 which means TRUE WHILE condition.
No While Loop It is as simple asinsert into #temp3 select title,const-row_number ()over(order by title) as consts,timestamp from #temp1select * from #temp3