Sybase updating a idnetity field.
Sybase updating a idnetity field.
Author
Discussion

ProPlus

Original Poster:

3,810 posts

271 months

Thursday 28th June 2007
quotequote all
have a table EC_Order

Has the identity field Order_Id which is currently at 5939, which is causing havoc.

I need to change Order_Id to 27001.

How?

Have tried:

update EC_Order
Set Order_Id = 27001
where syb_identity = 5939

and

set identity_insert EC_Order on

update EC_Order
Set Order_Id = 27001
where syb_identity = 5939

set identity_insert EC_Order off

but neither work.

Any ideas?


sybaseian

1,826 posts

306 months

Thursday 28th June 2007
quotequote all
Correcting identity column gaps

You can fix the rows containing the large numbers to correct gaps in the identity column. Perform the following steps:

1.

Turn on identity_insert.

1> set identity_insert table_name on

2> go

2.

Record the row(s) information. Because you cannot update an identity column, you must delete the row(s) and insert them again with the correct identity values.
3.

Correct all rows in error.
4.

bcp out the data from the table.
5.

Turn off identity_insert.

1> set identity_insert table_name off

2> go

6.

Drop the table and re-create it. This step resets the identity column's auto-start number to 1.
7.

bcp the data back into the table using the -E option to maintain the identity values that appear in the host file.


Note:

The -E option tells the server not to use the auto-insert identity numbers and to use the identity numbers included in the bcp data.

The next identity number should be one greater than the largest identity number in the table.



Hope this helps.

Ian