Updating with indexes: Our solution.

>We have problems in updating a key component of a record in a table
>for which an index as been created.
>
>We looked for a way to do the incrementation with an "order by ID3 desc",
>but there's no way to tell Oracle how to do its update.
>
>We tried to make the update in two pass.
>
>But it involves a lot of troubles to protect the integrity of the DB if
>many users are working on the same set of data.

I wrote that, last week, and I received a couple of suggestions. As people asked me, I'm now posting the solution we chose.

This is the two pass update.

In the first one, you can update the desired key component, but you have to change another of the components to make sure that the key overall will stay unique. So we chose to put that component 'x' to '-(x) - 1'. That way we are sure to get a unique key and the '- 1' prevents problems with the zero value.

In the second pass, you put the component 'x' to it's right value by setting it to '-(x + 1)'.

To protect the database integrity, we lock the table that we will be updating, before the first update. It should be impossible for two users working on the same set of data, to "steal" some of the other user's data by mixing their negative key values.

That's what we did.

And now, we keep going on our project and we will not be hesitating to ask other questions later on this summer if we encounter some big problems, given the great responses we received. Thanks to all of you !

Daniel Cote /\
Universite de Montreal |--| coted_at_jsp.umontreal.ca Montreal, Quebec \/ coted_at_ere.umontreal.ca Received on Thu Jul 15 1993 - 16:58:43 CEST