Post reply

Updating Identity Column

  • October 20, 2005 at 4:00 pm

    #169197

    Hi,

    How can I replace or update values in identity columns.Suppose if I want to update x with y in identity column.How can I do that?

    Any help is appreciated.

    -

    Thanks,

    sree

     

     

     

     

    Thanks,
    SR

  • October 20, 2005 at 5:35 pm

    #598986

    Well if you try you get a message as such.

    Server: Msg 8102, Level 16, State 1, Line 1

    Cannot update identity column 'pk'.

    So no you cannot easily do it.

    What are you trying to do?

    Is the identity column the primary key?

    why are you trying to assign it a different Value?

    Please post what your trying to do and someone can help you.

     

  • October 21, 2005 at 1:09 am

    #599020

    Assuming it is not a live table and you know what and why you want to do this, you can remove the identity from the column, update the column and switch identity on again.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • October 21, 2005 at 4:54 am

    #599061

    Another approach that might be more suitable in some circumstances is to use SET IDENTITY_INSERT <table> ON.

    You would then need to copy the record with the new ID, and delete the old one - probably best done as a stored procedure - something like (please note - not properly tested!):

    CREATE PROC UpdateIDTable(@oldid int, @newid int) AS

    BEGIN TRAN

    SET IDENTITY_INSERT IDTable ON

    INSERT INTO IDTable(ID, field1, field2)   -- List fields as appropriate

    SELECT @newid AS ID, field1, field2

    FROM IDTable

    WHERE ID = @oldid

    IF @@rowcount = 1    -- Check it worked!

    DELETE FROM IDTable

    WHERE ID = @oldid

    SET IDENTITY_INSERT IDTable OFF

    COMMIT

  • October 21, 2005 at 8:26 am

    #599138

    Remember if you use the above technique and you have child tables you must update the records in the child tables with the new key prior to deleting the old record (same caveat--not tested, but based on code we use):

    CREATE PROC UpdateIDTable(@oldid int, @newid int) AS

    BEGIN TRAN

    SET IDENTITY_INSERT IDTable ON

    INSERT INTO IDTable(ID, field1, field2)   -- List fields as appropriate

    SELECT @newid AS ID, field1, field2

    FROM IDTable

    WHERE ID = @oldid

    IF @@error<>0 BEGIN   --  Check for error and rollback if found
         ROLLBACK
         RETURN 1
    END

    IF @@rowcount = 1 BEGIN    -- Check it worked!

    UPDATE CHILDTABLE      -- Set all old keys to new key in child table
        SET ParentKey=@newid where ParentKey=@oldkey
        IF @@error<>0 BEGIN
              ROLLBACK
              RETURN 2
        END

    DELETE FROM IDTable        -- Now you can delete old parent record

    WHERE ID = @oldid

    SET IDENTITY_INSERT IDTable OFF

    COMMIT

    END

    ELSE BEGIN                       --    If @@rowcount<>1 then rollback
             ROLLBACK
             RETURN 3
    END

     

  • December 22, 2008 at 3:48 am

    #916036

    It is really helpfull for me. Thanks.

  • December 22, 2008 at 4:26 pm

    #916465

    Japie Botma (10/21/2005)

    Assuming it is not a live table and you know what and why you want to do this, you can remove the identity from the column, update the column and switch identity on again.

    Such code does not exist. Identity cannot be removed from a column unless you drop the column.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • December 22, 2008 at 4:28 pm

    #916466

    kashifyaqoob (12/22/2008)

    It is really helpfull for me. Thanks.

    Be very careful.... anytime you find the need to update or insert your own numbers into an IDENTITY column, you're probably doing something wrong.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 8 posts - 1 through 7 (of 7 total)

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