update and Identity Specification = yes

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

update and Identity Specification = yes

Author

Topic

feejaz
Yak Posting Veteran

68 Posts

Posted - 2009-01-01 : 08:02:57

How to update the record of the column which have Identity=true.
I am using this but found error like "Incorrect syntax near the keyword 'IDENTITY_INSERT'."

Update Contract
-- ContractNumber's Identity Specification = Yes,
-- and I want to update the value, therefore I am using this line
SET IDENTITY_INSERT [dbo].[Contract] ON
set ContractNumber = '900'
where ContractNumber = '374'
SET IDENTITY_INSERT [dbo].[Contract] OFF

I think "IDENTITY_INSERT" will be used for Inserting. May any one help me how to update the value through query.

regards,

feejaz

Navi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-01 : 11:05:31

Why don't you disable Identity on that column? And update .

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:07:15

quote:
Originally posted by feejaz

How to update the record of the column which have Identity=true.
I am using this but found error like "Incorrect syntax near the keyword 'IDENTITY_INSERT'."

Update Contract
-- ContractNumber's Identity Specification = Yes,
-- and I want to update the value, therefore I am using this line
SET IDENTITY_INSERT [dbo].[Contract] ON
set ContractNumber = '900'
where ContractNumber = '374'
SET IDENTITY_INSERT [dbo].[Contract] OFF

I think "IDENTITY_INSERT" will be used for Inserting. May any one help me how to update the value through query.

regards,

feejaz

Navi

it should be


-- ContractNumber's Identity Specification = Yes, 
-- and I want to update the value, therefore I am using this line
SET IDENTITY_INSERT [dbo].[Contract] ON

Update Contract
set ContractNumber = '900'
where ContractNumber = '374'

SET IDENTITY_INSERT [dbo].[Contract] OFF

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-01 : 13:13:46

Setting identity_insert on allows only insertion of new records with explicit values for the identity column. You cannot do an update on that column.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-01 : 13:27:31

Yup..you're right...i didn't notice its an update. you cant update identity column. You need to disable identity property before updating it.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-01 : 23:03:35

hi

I think it is possible to disable only the constraints(check,foreign)

Is it possible to disable identity property?

Jai Krishna

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-01 : 23:58:16

quote:
Originally posted by Jai Krishna

hi

I think it is possible to disable only the constraints(check,foreign)

Is it possible to disable identity property?

Jai Krishna

Yes Jai krishna...we can disable identity property and then we can update the identity column Value...

Left click on table name and then go to design mode and then change the identity specification as no in column properties and do the update on that identity column

feejaz
Yak Posting Veteran

68 Posts

Posted - 2009-01-02 : 07:20:10

Thanks for repling,

But i have not access to the database except run queries.

Please let me know in the query.

thanx

Navi

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-02 : 08:13:41

quote:
Originally posted by feejaz

Thanks for repling,

But i have not access to the database except run queries.

Please let me know in the query.

thanx

Navi

You need to have proper rights to disable identity.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-02 : 08:49:54

quote:
Originally posted by feejaz

Thanks for repling,

But i have not access to the database except run queries.

Please let me know in the query.

thanx

Navi

then you need to inform people with reqd access to do this change or tell them to give you access

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources