Updating primary key fields whilst maintaining referential integrity
Hint Ref: 020703010003
Hint Date: 01/03/2007
Hint Details:
PROBLEM:
Sybase will not allow you to change a field which refers to a field in another table unless there is already a record in existance witht he new field.
For example, if you want to change the linenumber of an accountlink record to one that does not already exist, you would need to update the line number of the corresponding IQacJournal record at the same time. Ordinarily, Sybase will not allow you to do this in a single update statement.
SOLUTION:
Before updating the fields, issue the following command:
SET OPTION WAIT_FOR_COMMIT = ON;
Now you can update each of the line numbers separately. Once complete, COMMIT the work and, providing the referential integity has been put back, the update will work correctly. Any errors and the transaction will be rolled back.
The SET option will only effect that session, so if you close the session immediately you have completed the work, you will not need to set the option OFF
(Please Note: This Procedure can be destructive and should only be used by Advanced Users. RADical Systems (UK) Limited or its Partners cannot be held responsible, in anyway, for any consequence of using this or any other Database Function, Procedure or SQL command. Responsibility resided solely with the user.
However, it is highly recommended that a full and valid Space Manager database backup is taken and verified before making any changes to the database.)