locked
How to update identity column value in sqlserver RRS feed

  • Question

  • Hi All,

    I have one table in production with identity column and there are some duplicate id's are inserted manually. Now i want to update the duplicate values with next increment values of identity column.

    Please help me on this.

    Regards,



    Vaishu

    Thursday, May 23, 2019 12:47 PM

Answers

  • Maybe...

    1. SET IDENTITY INSERT ON on table

    2. Delete and insert necessary rows with aimed IDs.

    3. SET IDENTITY INSERT OFF on table

    4. Reseed identity if necessary.

    Thursday, May 23, 2019 6:22 PM
  • Thanks for the quick response.

    Is there any way to disable or drop identity functionality.

    Best Regards,



    Vaishu

    Technically not, practically yes. You can create the same table (right click on the table, script, create table) omitting the identity. Call the table, table2. You can copy the data form table1 to table2. Drop table1 and rename table2 as table1. 

    Please mark as answer if this post helped you

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:15 AM
    Thursday, May 23, 2019 1:24 PM
  • You can not update a column with IDENTITY property.

    You need to move the data into another table and make the changes over there and insert the back the data into original table.

    Please mark as answer if this post helped you

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:15 AM
    Thursday, May 23, 2019 1:34 PM
  • Hi ,

    You can use SET IDENTITY_INSERT to insert new row and delete one of the duplicates .

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017


    Regards, David .

    Thursday, May 23, 2019 2:24 PM
  • You can not update a column with IDENTITY property, you have to insert the record with new value and delete the one with the old value.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:14 AM
    Thursday, May 23, 2019 12:55 PM
  • Again no, and I guess it has a reason why someone defined the column as identity.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:14 AM
    Thursday, May 23, 2019 1:17 PM

All replies

  • You can not update a column with IDENTITY property, you have to insert the record with new value and delete the one with the old value.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:14 AM
    Thursday, May 23, 2019 12:55 PM
  • Thanks for the quick response.

    Is there any way to disable or drop identity functionality.

    Best Regards,



    Vaishu

    Thursday, May 23, 2019 12:59 PM
  • Again no, and I guess it has a reason why someone defined the column as identity.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:14 AM
    Thursday, May 23, 2019 1:17 PM
  • Thanks for the quick response.

    Is there any way to disable or drop identity functionality.

    Best Regards,



    Vaishu

    Technically not, practically yes. You can create the same table (right click on the table, script, create table) omitting the identity. Call the table, table2. You can copy the data form table1 to table2. Drop table1 and rename table2 as table1. 

    Please mark as answer if this post helped you

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:15 AM
    Thursday, May 23, 2019 1:24 PM
  • You can not update a column with IDENTITY property.

    You need to move the data into another table and make the changes over there and insert the back the data into original table.

    Please mark as answer if this post helped you

    • Marked as answer by Olaf HelperMVP Saturday, August 17, 2019 6:15 AM
    Thursday, May 23, 2019 1:34 PM
  • Hi ,

    You can use SET IDENTITY_INSERT to insert new row and delete one of the duplicates .

    https://docs.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-2017


    Regards, David .

    Thursday, May 23, 2019 2:24 PM
  • Maybe...

    1. SET IDENTITY INSERT ON on table

    2. Delete and insert necessary rows with aimed IDs.

    3. SET IDENTITY INSERT OFF on table

    4. Reseed identity if necessary.

    Thursday, May 23, 2019 6:22 PM