Update Primary Key

Question

Update Primary Key

reach2sam

SSC Enthusiast

Points: 165
More actions
September 14, 2009 at 7:41 pm

#135924

Wondering, how can I update a primary key column using storeprocedure.
In asp.net I have 3 columns (code,description,active) which I am passing as a parameter to sp.
when update the record, I want to update all the 3 columns with given user input.
Please advice.

Viewing 4 posts - 1 through 3 (of 3 total)

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

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 1

You can update it like any other column.. But if you are updating it, then is it really a primary key? Primary keys generally don't change.

CEWII

reach2sam SSC Enthusiast Points: 165 More actions · Answer 2

Thanks and I know we normally dont update the primary key. but the situation is inthe middle as this table been used in many pages where I think hard to re-design the table. Is there any way to update the column.

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 3

The primary key column is like any other column with a unique constraint. If it is also the clustered index then you get dinged on the move from page to page..

UPDATE dbo.sometable

SET PrimaryKeyColumn = 'SomeNewValue'

WHERE PrimaryKeyColumn = 'SomeCurrentValue'

This query will run the first time and update the row but a second time will update no rows because 'SomeCurrentValue' is now set to 'SomeNewValue' and your where clause will not find it anymore.

CEWII