FETCH FIRST 1

[wawanz]

Hi guys...I have table Y with 700 records, with, say, two columns: column A, column B. I want to update column A with data from a column in table X but, table X only has 50 records. So what I'm trying to do is, update the first 50 rows of column A with data from table X, then loop back to the first row of table X to update the next 50 rows of table Y, and so on. Here's what I did:

DECLARE @szSuppId varchar(50), @counter int

DECLARE supp_cursor SCROLL CURSOR FOR
SELECT szSuppId FROM X
ORDER BY szSuppId

OPEN supp_cursor

FETCH NEXT FROM supp_cursor
INTO @szSuppId

SET @counter = 1

WHILE @@FETCH_STATUS = 0
BEGIN

   UPDATE [Y] SET szSuppId=@szSuppId
   WHERE CAST(szSuppId AS int)=@counter 
   
   SET @counter=@counter + 1

   FETCH NEXT FROM supp_cursor
   INTO @szSuppId
	
   IF NOT @@FETCH_STATUS = 0    ---return to the first row 
   BEGIN
     FETCH FIRST FROM supp_cursor
     INTO @szSuppId
   END

END

CLOSE supp_cursor
DEALLOCATE supp_cursor
GO

My problem is: I accomplished what I wanted, but the query seemed to go into an indefinite loop. After I stopped the query, and checked table Y, it did exactly what I intended. But it doesn't feel right because of the indefinite loop. Any idea on what I did wrong? Note: I use SQL Server 2005 Enterprise Edition.

Thanks guys.

 

[vongrunt]

Of course it loops forever - there is no BREAK condition that will terminate loop when there are no more rows in Y to update.

Btw. I'm pretty sure this can be done without cursor.

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[wawanz]

Oh I thought the @@FETCH_STATUS = 0 means something like the EOF in VB. Will try using the BREAK condition. Thank you.