Re: Changind a Primary Key value

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 08/05/04 

Date: Thu, 05 Aug 2004 14:19:51 +0200

On Thu, 5 Aug 2004 14:55:45 +0200, "Shai Goldberg"
<gshai(Remove-it)@shamir.co.il> wrote:

>Hi,
>
>I have a table with a Primary Key field that is an integer (int) data type
>with auto increment.
>
>Is there a way to change the value of the ID field (e.g. currently it is 100
>and I want to change it to 20, is it possible?)
>
>Thanks,
>

Hi Shai,

Do you mean that after inserting data, you want to manually change the ID
for one of the rows inserted?

Unfortunately, that is not possible.

>From Books Online:

Error 8102
Severity Level 16
Message Text
Cannot update identity column '%.*ls'.

Explanation
You have specifically attempted to alter the value of an identity column
in the SET portion of the UPDATE statement. You can only use the identity
column in the WHERE clause of the UPDATE statement.

Action
Updating of the identity column is not allowed. To update an identity
column, you can use the following techniques:

To reassign all identity values, bulk copy the data out, and then drop and
re-create the table with the proper seed and increment values. Then bulk
copy the data back into the newly created table. When bcp inserts the
values it will appropriately increase the values and redistribute the
identity values. You can also use the INSERT INTO and sp_rename commands
to accomplish the same action.

To reassign a single row, you must delete the row and insert it using the
SET IDENTITY_INSERT tblName ON clause.

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)

Relevant Pages

  • Re: Cannot update identity column PointOfSaleID.
    ... The sp_MSupd_PointOfSales stored procedure checks to see if the bit flag ... set and if so it executes an update statement which includes the identity ... for the identity column, it executes fine and chooses the else path. ...
    (microsoft.public.sqlserver.replication)
  • Re: auto-increment
    ... No, I don't need more than one identity column, I just wanted to have two ... Apparently an auto increment column should also be ... inserting auto increment values but currently have a data set that I need to ... auto increment field. ...
    (microsoft.public.sqlserver.programming)
  • Re: Create Delete Trigger on Table1 to Update a filed on Table2
    ... update statement. ... And who says that the table has an IDENTITY column? ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
    (comp.databases.ms-sqlserver)
  • strongly typed dataset update with identity column
    ... - I Have a table into SQL Server with an identity column (Auto increment) ... Dim dtChanges As DataTable = ds.Test.GetChanges ... I believe the problem is that when merging, the system doesn't see the 2 rows are the same... ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: removing IDENTITY attribute
    ... There is not command to add/remove the IDENTITY column property. ... create table tt(i int not null identity, ... alter table tt add dummy_i int ... --run update statement ...
    (microsoft.public.sqlserver.programming)