Sybase updating a idnetity field.
Discussion
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?
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?
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
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
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff


