Update Identity column

  • Comments posted to this topic are about the item Update Identity column

    Igor Micev,My blog: www.igormicev.com

  • Great question, thanks.

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Good question, but I had it wrong. I was thinking an UPDATE uses the special 'deleted' and 'inserted' tables. And therefor concluded the INDENTITY_INSERT also applies to UPDATE. I now understand these special tables only are for holding the results of the UPDATE and not how the UPDATE actually works.

    Always nice to learn something!!

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • Good Question. Easy One.

  • Good question, I've learnt something new

  • A very nice question and there may be chances of giving the wrong answer as with the "SET IDENTITY_INSERT" one can INSERT data into Identity Column but one can never UPDATE identity column.

  • This was removed by the editor as SPAM

  • Excellent question. Thanks for sharing.

  • The Question came as a real case experience with a software developer, and I had to do the update in that way, ... so thought to share it.

    Thanks.

    Igor Micev,My blog: www.igormicev.com

  • There's one more option to update IDENTITY i.e. using ALTER TABLE ... SWITCH. To be safe intermediate table column is declared as PRIMARY KEY. So original IDENTITY column must be PRIMARY KEY as well otherwise SWITCH will fail.

    -- table with the same but identity metadata

    -- PRIMARY KEY is needed to keep update safe.

    CREATE TABLE RolesNoIdentity

    (RoleID INTEGER PRIMARY KEY

    , Name VARCHAR(100)

    );

    --

    ALTER TABLE Roles SWITCH TO RolesNoIdentity;

    UPDATE dbo.RolesNoIdentity

    SET RoleID=-3

    WHERE RoleID=-2;

    ALTER TABLE RolesNoIdentity SWITCH TO Roles;

    DROP TABLE RolesNoIdentity;

    Sometimes it may help.

  • Igor Micev (11/17/2014)


    The Question came as a real case experience with a software developer, and I had to do the update in that way, ... so thought to share it.

    Thanks.

    +1000, So true and QotD is the best place to share it. 🙂

  • serg-52 (11/17/2014)


    There's one more option to update IDENTITY i.e. using ALTER TABLE ... SWITCH. To be safe intermediate table column is declared as PRIMARY KEY. So original IDENTITY column must be PRIMARY KEY as well otherwise SWITCH will fail.

    -- table with the same but identity metadata

    -- PRIMARY KEY is needed to keep update safe.

    CREATE TABLE RolesNoIdentity

    (RoleID INTEGER PRIMARY KEY

    , Name VARCHAR(100)

    );

    --

    ALTER TABLE Roles SWITCH TO RolesNoIdentity;

    UPDATE dbo.RolesNoIdentity

    SET RoleID=-3

    WHERE RoleID=-2;

    ALTER TABLE RolesNoIdentity SWITCH TO Roles;

    DROP TABLE RolesNoIdentity;

    Sometimes it may help.

    Very good from your side. Thanks.

    Igor Micev,My blog: www.igormicev.com

  • Out of interest, what was the reason for needing to make this update? I can understand inserts (sort of) due to the need to support legacy data, but not clear on why you'd want to update an identity column?

  • The update was incomplete as well. The requirement was to update two rows, so the SQL should have read

    SET IDENTITY_INSERT dbo.Roles ON

    UPDATE dbo.Roles

    SET RoleID=-3

    WHERE RoleID=-2;

    UPDATE dbo.Roles

    SET RoleID=-2

    WHERE RoleID=-1;

    SET IDENTITY_INSERT dbo.Roles OFF

    or something like that. Of course, the UPDATE was going to fail anyway, so the point is moot.

  • Toreador (11/17/2014)


    Out of interest, what was the reason for needing to make this update? I can understand inserts (sort of) due to the need to support legacy data, but not clear on why you'd want to update an identity column?

    Business and analytical requirement related with migration. Why not?

    Igor Micev,My blog: www.igormicev.com

Viewing 15 posts - 1 through 15 (of 26 total)

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