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);
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…