Changind a Primary Key value

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,
Shai,shalom
CREATE TABLE Test (col INT IDENTITY(1,1))
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--4 rows
GO
DBCC CHECKIDENT (Test, RESEED, 2)
GO
INSERT Test DEFAULT VALUES
INSERT Test DEFAULT VALUES
GO
SELECT * FROM Test--6 rows
GO
DROP TABLE Test
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> 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,
>
|||Shai
You will get an error of primary key violention. This is one of many reasons
not to use an identity property as a primary key,
as you cannot update it.
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> 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,
>
|||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)
|||Something like this will work
drop table t
go
create table t( id int identity(1,1) primary key, data varchar(8) not null)
go
insert into t values('HI')
go
select * from t
GO
set identity_insert t on
insert t (id, data) select 2, data from t where id = 1
delete from t where id = 1
GO
set identity_insert t off
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Shai Goldberg" <gshai(Remove-it)@.shamir.co.il> wrote in message
news:OHJPAKueEHA.236@.tk2msftngp13.phx.gbl...
> 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,
>
sql