Merge sql throws Unique constraint violation error

Question

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);

i have corrected the question now – Symonds Jan 11 '21 at 13:49 — Symonds, Jan 11 '21 at 13:49

score 1 · Accepted Answer · answered Jan 11 '21 at 12:24

1

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).

answered Jan 11 '21 at 12:24

Littlefoot

131,892
15
35
57

ID_LL, ID_UU, TKR datatype is varchar2 – Symonds Jan 11 '21 at 13:31
OK, so instead of -1 use some character. – Littlefoot Jan 11 '21 at 13:35
i dont know how but with -1 also its working but will i create problem later on ? – Symonds Jan 12 '21 at 09:43

score 1 · Answer 2 · answered Jan 11 '21 at 12:27

1

Yes, As mentioned in the other answer also, cause of the error is s.ID_LL=h.ID_LL. You can update it as

( s.ID_LL=h.ID_LL OR (s.ID_LL is null and h.ID_LL is null) )

OR

( s.ID_LL=h.ID_LL OR coalesce(s.ID_LL, h.ID_LL) is null )

answered Jan 11 '21 at 12:27

Popeye

35,427
4
10
31

now i am facing performance issue ...i tried both sql and my merge query is taking more than 20 minutes and its still running..without this condition it runs within few seconds...i have 800000 rows in my table TEST_RUA_MER...i tried to remove index on this table and still merge query is taking too much time .. – Symonds Jan 11 '21 at 14:01
can i create may be functional index here ? – Symonds Jan 11 '21 at 14:35

Merge sql throws Unique constraint violation error

2 Answers2