Updating with indexes

We have problems in updating a key component of a record in a table for which an index as been created. The context is:

Table: A

Fields:  ID1    NOT NULL NUMBER(10)
         ID2    NOT NULL NUMBER(10)
         ID3    NOT NULL NUMBER(10)
         COST   NUMBER

WHERE ID1 = :BLOCKA.VAL1 AND
      ID2 = :BLOCKA.VAL2 AND
      ID3 > :BLOCKB.VAL1;

ORA-00001: Duplicate key in index.

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. Something like:

Pass 1:

update A
set ID3 = ID3 + 1,

    ID1 = "A temporary special value"

Pass 2:

update A
set ID1 = "The right value"

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.

So what are you suggesting to solve that problem ? Any help would be greatly appreciated !

Daniel Cote /\
Universite de Montreal |--| coted_at_jsp.umontreal.ca Montreal, Quebec \/ coted_at_ere.umontreal.ca Received on Fri Jul 09 1993 - 22:09:36 CEST