Merge sql throws Unique constraint violation error

I have below two table for which when i query table TEST_RUA:

select  CLASS, ID_LL, ID_UU, TKR from TEST_RUA   where ID_UU= 'GV9999B12M0'

it returns:

CLASS    ID_LL   ID_UU          TKR
Bond    (null)  GV9999B12M0     WIB

When i query table TEST_RUA_MER:

select CLASS, ID_LL, ID_UU, TKR from TEST_RUA_MER   where ID_UU= 'GV9999B12M0'

it returns:

CLASS    ID_LL   ID_UU          TKR
Bond    (null)  GV9999B12M0     WIB

You can see both the values are same for table where ID_UU= 'GV9999B12M0'. The table TEST_RUA_MER has unique index on columns ID_LL, ID_UU, TKR.

Now i have below merge query which throws error as ORA-00001: unique constraint violated and i dont understand how can i avoid this error as both the table values are same then in this case this merge query should try to update and not to insert in table TEST_RUA_MER .

merge into TEST_RUA_MER h   using  (    
select distinct r.CLASS, r.ID_LL, r.ID_UU, r.TKR from TEST_RUA r   ) s 
on (s.ID_LL=h.ID_LL and s.ID_UU=h.ID_UU  and s.TKR=h.TKR) when matched then   
update set h.CLASS = s.CLASS, h.ID_LL = s.ID_LL, h.ID_UU = s.ID_UU, h.TKR = s.TKR
when not matched then   insert values (s.CLASS, s.ID_LL, s.ID_UU, s.TKR);

Answer

Looks like NULL causes problems; it isn’t “equal” to anything, so this:

on (s.ID_LL=h.ID_LL

fails.

Try with

on (nvl(s.ID_LL, -1) = nvl(h.ID_LL, -1)

(depending on what ID_LL column’s datatype is; I presumed it is a number).

Advertisement

Answer