How to swap two unique values in a table?
(SQL Anywhere 16)
Thanks for any comments.
asked 02 Apr '14, 09:02
It is a bug in INSERT ... ON EXISTING UPDATE. I have filed a bug report (engineering case 761289).
As @Volker suggests, the MERGE statement works well as a workaround (shown below).
When an UPDATE statement causes a uniqueness violation error during processing it might be a problem resolved by later rows. The offending row is removed from the base table and moved to a "HOLD TABLE". After all rows are processed by the UPDATE, the rows in the HOLD TABLE are inserted into the base table and constraints are re-checked to see if the UPDATE resolved the problem after completing. See the help topics for Locks during updates and Data changes using UPDATE for more details.
When an INSERT ... ON EXISTING UPDATE statement encounters a uniqueness error, the row is also moved to the HOLD TABLE but there is no subsequent insert from the HOLD TABLE into the base table, leading to an unexpected delete.
A workaround is to use the MERGE statement, for example:
HOLD TABLE processing of MERGE statements is not affected by this issue.
In version 8.0 through 9.0, the uniqueness violation during the INSERT...ON EXISTING statement would generate an error instead of deleting the row. In versions 10.0 and above the row is moved to the HOLD TABLE but not re-added to the base table, leading to the missing row. The MERGE statement is generally preferred to the INSERT..ON EXISTING as it clearer what the operation should do (e.g., which index is used) and offers more flexibility in defining the actions. I do admit I had to review the syntax in the documentation for MERGE while the INSERT..ON EXISTING is stuck in my mind, but I do prefer the MERGE.