This is a tough one to explain because my test case below does not reproduce the error. Here goes anyway. I have a table containing both historical and current data. For an id there can be many historical records but only one current record. I use the pattern described below for the unique index to enforce this. The intention of the merge is that when I have a new current record to replace the existing current I want the merge to first update the existing current to non-current then insert the new current record. I use this same pattern in merge statements throughout my application with no problems, but every few months one merge statement throws a ORA-00001: unique constraint error. It seems that it is doing the insert before the update and so hitting the unique constraint. If I remove the unique constraint the statement runs OK and afterwards I can recreate the unique constraint ie there are no duplicates. I have tried sorting the using subquery to force the update to occur first with no effect. As a workaround I have changed the merge into a cursor for loop that first tries to update then if sql%rowcount = 0 does an insert. This works OK but I would like to find out how oracle decides the order in which to insert or update in a merge and is there anyway to control it, especially as I use the pattern in many places which may potentially throw the unique constraint error in future.
Thanks for your help.
create table t1 (id number, id_seq number, status varchar2(20))
/
-- unique index to enforce only one 'current' record for each id
create unique index t1_u1 on t1 (nvl2(status,id||status,null))
/
insert into t1 values (1,10,'current' );
insert into t1 values (1,9,null );
insert into t1 values (1,8,null );
insert into t1 values (2,7,'current' );
insert into t1 values (2,6,null );
insert into t1 values (2,5,null );
-- this works in this example, but I have a case (too complex to put here) where a similar statement does not work
merge into t1 b
using (
-- new current record to be inserted
select
1 id
, 11 id_seq -- unmatched
, 'current' new_status
from dual
union all
-- existing current record to be updated to non-current
select
1 id
, 10 id_seq
, null new_status
from dual
) a
on (
b.id = a.id
and b.id_seq = a.id_seq
)
when matched then
-- found a current record, make it non-current
update
set status = a.new_status
when not matched then
-- insert a new current
insert (id, id_seq, status)
values (a.id, a.id_seq, a.new_status)
/