need a substitute for a CURSOR 1

[faccorp]

Hi,

I'm looking for a better way to loop through a table. I'm using a Cursor and it's very slow. I know cursors are bad and I guess this is one reason. Here is my cursor

DECLARE @idnum1 INT
DECLARE @date1 DATETIME

DECLARE my_sucky_cursor CURSOR FOR
SELECT [MyTable].[ID Number], [MyTable].[DateTo]
FROM [MyTable] WHERE [MyTable].[Type] = 0

OPEN my_sucky_cursor
FETCH NEXT FROM my_sucky_cursor INTO @idnum1, @date1
WHILE(@@fetch_status = 0)
BEGIN
  UPDATE [MyTable]
  SET [MyTable].[AgingDate] = @date1
  WHERE ([MyTable].[Reference] = @idnum1)
  FETCH NEXT FROM my_sucky_cursor INTO @idnum1, @date1
END

CLOSE my_sucky_cursor
DEALLOCATE my_sucky_cursor

This table could have 200-300K or more records. Worked great until I tried using it on a clients real world database.

Thanks!

 

[ca8msm]

What do you want to do? Are you just trying to update the "AgingDate" to equal the "DateTo" field for every row where the "Type" equals zero?


-------------------------------------------------------

Mark,
[URL unfurl="true"]http://aspnetlibrary.com[/url]
[URL unfurl="true"]http://mdssolutions.co.uk[/url] - Delivering professional ASP.NET solutions
[URL unfurl="true"]http://weblogs.asp.net/marksmith[/url]

 

[psperry2]

How about a #temp_table instead of a cursor:

SELECT [MyTable].[ID Number], [MyTable].[DateTo]
INTO #Temp_table
FROM [MyTable] WHERE [MyTable].[Type] = 0

 

[kaht]

I believe this should work. When updating a table and joining the table to itself, I believe you must leave one of the tables w/o an alias, that's why you see the MyTable.Type typed out fully instead of using an alias.

[COLOR=blue]update[/color] MyTable
[COLOR=blue]set[/color] AgingDate = mt.DateTo
[COLOR=blue]from[/color] MyTable
   [COLOR=blue]inner[/color] [COLOR=blue]join[/color] MyTable mt
   [COLOR=blue]on[/color] mt.Reference = MyTable.[ID [COLOR=blue]Number[/color]]
[COLOR=blue]where[/color] MyTable.Type = 0

-kaht

Lisa, if you don't like your job you don't strike. You just go in every day and do it really half-assed. That's the American way. - Homer Simpson
[small]<P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <B> <P> <.</B> bites again.[/small]

 

[faccorp]

For each record whose type = 0 (parent records), there will be other child records that are associated with the parent record. I want to set the child 'AgingDate' to the 'DateTo' of the parent.

Thanks

 

[faccorp]

thanks everybody.

kaht. Your method works very well. Thank You!