Update table going in endless loop

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

Update table going in endless loop

Author

Topic

adityaG
Starting Member

10 Posts

Posted - 2010-08-13 : 10:40:37

Hi all ,

I am new to the sql server .I am trying to update a column having more than 2millon row.
I am giving you the sample data and the condition .

Create table Mytable( id varchar (4) not null , Rate varchar(8), Startdate date not null , enddate date )

INSERT INTO Mytable Values ( '1005', '2200', '2004-01-01', null)
INSERT INTO Mytable Values ( '1005', '2200', '2005-01-01', null)
INSERT INTO Mytable Values ( '1005', '2300', '2005-01-01', null)
INSERT INTO Mytable Values ( '1005', '2300', '2006-02-01', null)
INSERT INTO Mytable Values ( '1005', '2400', '2006-01-01', null)
INSERT INTO Mytable Values ( '1005', '2400', '2006-06-01', null)
INSERT INTO Mytable Values ( '1005', '2500', '2006-08-31', null)
INSERT INTO Mytable Values ( '1005', '2500', '2007-01-01', null)
INSERT INTO Mytable Values ( '1005', '2600', '2007-03-04', null)
INSERT INTO Mytable Values ( '1005', '2600', '2007-06-08', null)
INSERT INTO Mytable Values ( '1006', '2700', '2007-08-11', null)
INSERT INTO Mytable Values ( '1006', '2700', '2007-01-01', null)
INSERT INTO Mytable Values ( '1006', '2800', '2007-01-01', null)
INSERT INTO Mytable Values ( '1006', '2800', '2008-01-01', null)
INSERT INTO Mytable Values ( '1006', '2900', '2007-01-01', null)
INSERT INTO Mytable Values ( '1006', '2900', '2008-01-01', null)

there are more than 2million records in the table .
Now i want to update endate by using DaTEADD(dd,-1,startdate) i.e previous day of startday.
But the catch is , i want to update endate of 1st row , using previous day of startdate of 2nd row for same rate .

So the output should be like 1005 ,2200 , 2004-01-01 , 2004-12-31
1005, 2200 , 2005-01-01 , 2005-12-31
1005, 2300 , 2005-01-01 , 2005-01-31
1005, 2300 , 2006-02-01 , 2006-01-31

I dont have a unique column , first 3 columns makes a composite key for me .Hence i am using a temp table using row_number function to insert rowid , then left joining the table using tableA .rowid= tableb.rowid + 1 . Then using the temp table , i am updating original table with matching the 1 st three columns . All this i am doing in a while loop ... But this isnt working for me as the loop never ends , or may be it will take two days .

Can anybody solve this problem , or post any example of cursor or while loop , so that i can work on this ...

Thanks in advance

adityaG
Starting Member

10 Posts

Posted - 2010-08-13 : 11:00:21

sorry , I inserted wrong data . But just consider the dates as it is .

adityaG
Starting Member

10 Posts

Posted - 2010-08-13 : 11:15:13

Nobody to help :-(

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-13 : 11:35:58

How do you handle the "last" day in a sequence?

For example: '1005', '2600', '2007-06-08',

Has no other StartDate after it for that ID and Rate. Assuming that you want the StartDates grouped by ID and Rate.

Also, if this is for a warehouse, then the EndDate should be the same as the StartDate of the next record. That way you avoid any temporal gaps.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-08-13 : 11:36:31

So enddate is always startday-1 of next row but not if id and Rate are different.
So what should be the endate when there is no next row?

Please give correct sample data and correct sample output!

No, you're never too old to Yak'n'Roll if you're too young to die.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-08-13 : 11:46:14

does this do it?


IF OBJECT_ID('tempdb..#Mytable') IS not NULL 
  BEGIN 
    drop table #Mytable 
  END 
IF OBJECT_ID('tempdb..#RealTable') IS not NULL 
  BEGIN 
    drop table #RealTable 
  END 



Create table #RealTable( id varchar (4) not null , Rate varchar(8), Startdate date not null , enddate date )
Create table #Mytable( RN int, id varchar (4) not null , Rate varchar(8), Startdate date not null , enddate date )


INSERT INTO #RealTable 
select '1005', '2200', '2004-01-01', null
union all select  '1005', '2200', '2005-01-01', null
union all select  '1005', '2300', '2005-01-01', null
union all select  '1005', '2300', '2006-02-01', null
union all select  '1005', '2400', '2006-01-01', null
union all select '1005', '2400', '2006-06-01', null
union all select  '1005', '2500', '2006-08-31', null
union all select  '1005', '2500', '2007-01-01', null
union all select  '1005', '2600', '2007-03-04', null
union all select  '1005', '2600', '2007-06-08', null
union all select  '1006', '2700', '2007-08-11', null
union all select  '1006', '2700', '2007-01-01', null
union all select  '1006', '2800', '2007-01-01', null
union all select  '1006', '2800', '2008-01-01', null
union all select  '1006', '2900', '2007-01-01', null
union all select  '1006', '2900', '2008-01-01', null

insert into	
	#Mytable (RN, id, Rate, Startdate, enddate )
select	
	ROW_NUMBER() over(partition by id, rate order by id,rate) as RN
	,id, Rate,Startdate, enddate 
from
	#RealTable

select 
	a.rn, a.id, a.Rate , a.Startdate
	--*EDIT dont select this*,b.rn, b.id, b.Rate , b.Startdate
	, coalesce(DaTEADD(dd,-1,b.startdate),(dateadd(dd,-1,(DaTEADD(mm,1,a.startdate))))) as NewEndDate
from 
	#mytable a
left join
	#Mytable b
on
	a.RN = b.RN - 1
and
	a.id = b.id
and
	a.Rate = b.Rate

Edit for clarity. change the select into an update.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

adityaG
Starting Member

10 Posts

Posted - 2010-08-13 : 12:43:14

The enddate of the last row should be null .

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-08-13 : 13:28:04

then change the coalesce to a case.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

adityaG
Starting Member

10 Posts

Posted - 2010-08-15 : 10:14:40

Hi ,

Unfortunately i am not able to apply the case function instead of coalesce .Can you help me in using case .
Some urgent help is needed .

Thanks in advance

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 10:51:39

something like this i suppose

case when b.RN is not null then coalesce(DaTEADD(dd,-1,b.startdate),(dateadd(dd,-1,(DaTEADD(mm,1,a.startdate))))) else null end as NewEndDate

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

adityaG
Starting Member

10 Posts

Posted - 2010-08-15 : 11:07:10

Thanks ,
But this update taking too long to update 2million rows .performance wise its too degrading. Should i use a while loop or any other suggestions . For while loop , when the condition reaches to the last row it doesnt found the last row hence it never breaks .What should i do in this case ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 11:56:55

do you have any index on table?

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

adityaG
Starting Member

10 Posts

Posted - 2010-08-15 : 12:06:50

sorry . I have clustered index on first three columns which is in ascending order

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-15 : 12:33:12

check your execution plan and see if its using the index

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

adityaG
Starting Member

10 Posts

Posted - 2010-08-15 : 12:44:25

Yes its using the index

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-08-17 : 11:59:17

i have NO idea why you think a loop would perform better than a set based solution.
If it is taking WAY too long, then it will just have to be run during off peak hours.

Its been 4 days, so i forget what i meant by use a case instead of coalesce

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2010-08-17 : 12:10:58

is it me...but I don't see an order by anywhere

The order of data in a relational database has no meaning

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2010-08-17 : 15:12:44

maybe i should have used the order by in the over clause *oops*

again, best guess shot in the dark.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources