Post reply

Cursor updating all records.

  • March 31, 2005 at 4:40 pm

    #65839

    Why is this script updating every record in the table? The table has 197 records in it but only 88 have a CRED_ID of NULL, therfore it should only update the 88 records.

    DECLARE @Value INT, @CompanyName VARCHAR(50)

    SET @Value = 1

    DECLARE XYZ CURSOR

    FOR

    SELECT CompanyName

    FROM WorkSite

    WHERE CRED_ID IS NULL

    OPEN XYZ

    FETCH NEXT FROM XYZ INTO @CompanyName

    WHILE (@@FETCH_STATUS) = 0

    BEGIN

     UPDATE WorkSite SET CRED_ID = @Value

     FETCH NEXT FROM XYZ INTO @CompanyName

     SET @Value = @Value + 1

    END

    CLOSE XYZ

    DEALLOCATE XYZ

    Kindest Regards,

  • March 31, 2005 at 4:52 pm

    #549945

     

    You're updating the table instead of the cursor position!

    WHILE (@@FETCH_STATUS) = 0

    BEGIN

     UPDATE WorkSite SET CRED_ID = @Value

     Where Current Of XYZ  <---- Add this

     FETCH NEXT FROM XYZ INTO @CompanyName

     SET @Value = @Value + 1

    END

    Edited to show Where Current OF

  • March 31, 2005 at 4:55 pm

    #549946

    where in update?

    the code above update all (197) rows 197 times.

    put current of or unique where in update statement.

  • March 31, 2005 at 5:13 pm

    #549950

    I thought that the initial select for the cursor would have catered for that but nevertheless I have changed it now to;

    UPDATE WorkSite SET CRED_ID = @Value

     WHERE CRED_ID IS NULL

    but the @Value always remains 1. It doesn't increase.

    Kindest Regards,

  • March 31, 2005 at 5:15 pm

    #549951

    I forgot to mention that the the CRED_ID COLUMN is a VARCHAR(10)

    Kindest Regards,

  • March 31, 2005 at 5:29 pm

    #549955

    Please see my edited post above.

     

  • March 31, 2005 at 5:33 pm

    #549957

    Why are you using a cursor ?

    Select Identity(int, 1, 1) As NewValue,

      WorkSitePKEY

    Into #Sequence

    From WorkSite

    Where Cred_ID IS NULL

    Update WorkSite

    Set Cred_ID = Cast( s.NewValue As varchar(10) )

    From WorkSite As w

    Inner Join #Sequence On s

    Where s.WorkSitePKEY = w.WorkSitePKEY

     

    [Edit] Just noticed the varchar(10) requirement and edited as required

  • March 31, 2005 at 5:33 pm

    #549956

    I'm sure with over 300 posts on this site you have heard of the CAST or CONVERT functions.

     

  • March 31, 2005 at 5:46 pm

    #549958

    No need to perfrom a convert/cast Ron K. WHERE CURRENT OF will do the trick. The SET BASED solution from PW is attractive as well.

    Thanks for your help folks.

    Kindest Regards,

  • March 31, 2005 at 5:53 pm

    #549960

    The set-based solution will scale better to a larger table. It will also translate easily to the new ROW_NUMBER ranking function coming in Sql Server 2005 that will get rid of the temp table part and make it 1 nice clean update out of a ranked derived table.

     

  • March 31, 2005 at 6:46 pm

    #549968

    No worries thanks.

    Kindest Regards,

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply