Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Update autoincrement column in SQL server

I have a need to change ONE PK value in one row of a table of a SQLSERVER 2005 databsae. The PK is currently auto-incrementing.

One possibility is to temporarily remove the auto-increment from the key, make the modification, and put it back.

The database is in production. Is there a way to safely make this change without taking the DB down?

Why? My customer wants a specific ID for one of the records.

What am I afraid of? I'm afraid of records being added in the DB while I've modified the key, and it somehow messing up an index on another table that uses the field as an FK? Am I nuts?

like image 570
jm. Avatar asked Mar 27 '09 18:03

jm.


People also ask

How update a column with auto increment in SQL Server?

The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature. In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record. Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change it to IDENTITY(10,5) .

How do I change auto increment?

In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.

How do you reset the column values in a auto increment identity column?

In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.

How do I create an existing column auto increment in SQL Server?

If you're looking to add auto increment to an existing table by changing an existing int column to IDENTITY , SQL Server will fight you. You'll have to either: Add a new column all together with new your auto-incremented primary key, or. Drop your old int column and then add a new IDENTITY right after.


3 Answers

SET IDENTITY_INSERT yourTable ON

INSERT INTO yourTable (col1, col2, col3)
select newID, col2, col3
FROM yourTable
WHERE currentID = xxx

SET IDENTITY_INSERT yourTable OFF

You may want to lock the table to prevent other inserts from failing while IDENTITY_INSERT is on.

EDIT: Well crap, I forgot the obvious bit about deleting the original record.

like image 182
Some Canuck Avatar answered Sep 22 '22 10:09

Some Canuck


Updating doesn't work - you'd have to insert the new record and delete the old one instead...

set identity_insert mytable on

insert mytable (myidentitycolumn, someothercolumn) 
select 42, someothercolumn
from mytable
where myidentitycolumn = 1;

delete mytable where myidentitycolumn = 1;

set identity_insert mytable off
like image 23
Scott Ivey Avatar answered Sep 24 '22 10:09

Scott Ivey


You can only update via dropping the identity and re-creating it. However, you could also set identity insert ON (http://msdn.microsoft.com/en-us/library/aa259221(SQL.80).aspx) and insert a new row with the identity val you want, turn ident insert off, then delete the old one. Why do you want to do this?

like image 34
Matt Rogish Avatar answered Sep 23 '22 10:09

Matt Rogish