Home » SQL & PL/SQL » SQL & PL/SQL » Merge is not working properly (Oracle 11g)
Merge is not working properly [message #582839] Tue, 23 April 2013 01:04 Go to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Hi All,

I am using the following merge statement it's not working properly.
If I tried to insert (or) update the existed record.
I am getting unique key constraint violated error.

DECLARE
p_buid NUMBER;
p_ordernum NUMBER;
P_score NUMBER;
p_scoretype VARCHAR2(20);
BEGIN
p_buid:=11;
p_ordernum:=404189565;
P_score :=2000;
P_SCORETYPE:='V';
merge into score_filter_apply s
using (select buid,order_num,received_date_time,
velocity_score,negative_score,trend_score,
vendor_combined_score,vendor_bt_score,vendor_st_score
from score
) p
on (s.buid = p.buid
and s.order_num = p.order_num
and s.buid = p_buid
and s.order_num = p_ordernum
)
when matched
then update
set velocity_score = p.velocity_score,
negative_score = p.negative_score,
trend_score = p.trend_score,
vendor_combined_score = p.vendor_combined_score,
vendor_bt_score = p.vendor_bt_score,
vendor_st_score = p.vendor_st_score
when not matched
then insert (buid,order_num,received_date_time)
values (p.buid,p.order_num,sysdate);
END;
/

I tried this I am able to get the record.

SELECT * FROM score_filter_apply s,
(select buid,order_num,received_date_time,
velocity_score,negative_score,trend_score,
vendor_combined_score,vendor_bt_score,vendor_st_score
from score
) p
WHERE (s.buid = p.buid
and s.order_num = p.order_num
and s.buid = 11
and s.order_num = 404189565
);

Please help me.
Thanks.

[EDITED by LF: fixed topic title which had 2 "not"s]

[Updated on: Fri, 26 April 2013 00:10] by Moderator

Report message to a moderator

Re: merge is not working not properly [message #582846 is a reply to message #582839] Tue, 23 April 2013 02:01 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Quote:
ORA-00001: unique constraint (string.string) violated
Cause: An UPDATE or INSERT statement attempted to insert a duplicate key. For Trusted Oracle configured in DBMS MAC mode, you may see this message if a duplicate entry exists at a different level.
Action: Either remove the unique restriction or do not insert the key.


regards,
Delna

[Updated on: Tue, 23 April 2013 02:01]

Report message to a moderator

Re: merge is not working not properly [message #582851 is a reply to message #582839] Tue, 23 April 2013 02:18 Go to previous messageGo to next message
dariyoosh
Messages: 531
Registered: March 2009
Location: Iran / France
Senior Member
ajaykumarkona wrote on Tue, 23 April 2013 08:04
Hi All,

I am using the following merge statement it's not working properly.
If I tried to insert (or) update the existed record.
I am getting unique key constraint violated error.

. . .
merge into score_filter_apply s
. . .
when not matched
then insert (buid,order_num,received_date_time)
values (p.buid,p.order_num,sysdate);
. . .



The not matched clause in your code could generate multiple times the same row.

Regards,
Dariyoosh

[Updated on: Tue, 23 April 2013 02:52]

Report message to a moderator

Re: merge is not working not properly [message #582866 is a reply to message #582839] Tue, 23 April 2013 03:02 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please FORMAT your code, this does NOT mean justa dd code tags but also properly INDENT it.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: merge is not working not properly [message #582882 is a reply to message #582866] Tue, 23 April 2013 03:42 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I am sending the tables and data.

Please help me.
CREATE TABLE SCORE_FILTER_APPLY
(
  VELOCITY_SCORE         NUMBER(4),
  NEGATIVE_SCORE         NUMBER,
  TREND_SCORE            NUMBER,
  VENDOR_BT_SCORE        NUMBER,
  VENDOR_ST_SCORE        NUMBER,
  VENDOR_COMBINED_SCORE  NUMBER,
  BT_ST_VENDOR_CODE      VARCHAR2(25 CHAR),
  SENT_DATETIME          DATE,
  BUID                   NUMBER(6)              NOT NULL,
  ORDER_NUM              VARCHAR2(18 CHAR)      NOT NULL,
  RECEIVED_DATE_TIME     DATE,
  VENDOR_SCORED          VARCHAR2(1 CHAR)
);

ALTER TABLE SCORE_FILTER_APPLY ADD (
  CONSTRAINT PK_SCORE_FILTER_APPLY
 PRIMARY KEY
 (ORDER_NUM, BUID));

CREATE TABLE SCORE
(
  VELOCITY_SCORE         NUMBER(4),
  NEGATIVE_SCORE         NUMBER,
  TREND_SCORE            NUMBER,
  VENDOR_BT_SCORE        NUMBER,
  VENDOR_ST_SCORE        NUMBER,
  VENDOR_COMBINED_SCORE  NUMBER,
  BUID                   NUMBER(6)              NOT NULL,
  ORDER_NUM              VARCHAR2(18 CHAR)      NOT NULL,
  RECEIVED_DATE_TIME     DATE,
  VENDOR_SCORED          VARCHAR2(1 CHAR)
);

ALTER TABLE SCORE ADD (
  PRIMARY KEY
 (BUID, ORDER_NUM);


insert into score (buid,order_num,received_date_time)
values (1,1,sysdate);

insert into score (buid,order_num,received_date_time,negative_score)
values (2,2,sysdate,5);

insert into SCORE_FILTER_APPLY (buid,order_num,received_date_time)
values (1,1,sysdate);

insert into SCORE_FILTER_APPLY (buid,order_num,received_date_time,negative_score)
values (2,2,sysdate,5);


Thanks in advance.
Re: merge is not working not properly [message #582885 is a reply to message #582882] Tue, 23 April 2013 03:54 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.


Regards
Michel
Re: merge is not working not properly [message #582890 is a reply to message #582885] Tue, 23 April 2013 04:04 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
If I use the values 3,3,'V',10
New record has to be inserted
If I use the values 2,2,'X',7
The existed record has to be updated.
Re: merge is not working not properly [message #582894 is a reply to message #582890] Tue, 23 April 2013 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
If I use the values 3,3,'V',10


Which come from what?
We only know the 2 tables:
VELOCITY_SCORE NEGATIVE_SCORE TREND_SCORE VENDOR_BT_SCORE VENDOR_ST_SCORE
-------------- -------------- ----------- --------------- ---------------
VENDOR_COMBINED_SCORE       BUID ORDER_NUM          RECEIVED_DATE_TIME  V
--------------------- ---------- ------------------ ------------------- -

                               1 1                  23/04/2013 11:19:45

                            5
                               2 2                  23/04/2013 11:19:45


2 rows selected.

SQL> select * from SCORE_FILTER_APPLY;

VELOCITY_SCORE NEGATIVE_SCORE TREND_SCORE VENDOR_BT_SCORE VENDOR_ST_SCORE
-------------- -------------- ----------- --------------- ---------------
VENDOR_COMBINED_SCORE BT_ST_VENDOR_CODE         SENT_DATETIME             BUID
--------------------- ------------------------- ------------------- ----------
ORDER_NUM          RECEIVED_DATE_TIME  V
------------------ ------------------- -

                                                                             1
1                  23/04/2013 11:19:45

                            5
                                                                             2
2                  23/04/2013 11:19:45


2 rows selected.

Regards
Michel
Re: merge is not working not properly [message #582898 is a reply to message #582894] Tue, 23 April 2013 05:07 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
I am sending the complete code.

If I execute the following procedure as below.

begin setscores(3,3,'V',10); end;

New record has to be inserted into SCORE and score_filter_apply tables.

If I execute as begin setscores(2,2,'X',7); end;
Existed record has to be updated in both the SCORE and score_filter_apply tables.

CREATE OR REPLACE procedure setscores1(p_buid      in number,
                    p_ordernum  in number,
                    p_scoretype in varchar2,
                    p_score     in number
                   ) is
begin
 
  merge into score s
  using (select p_buid buid,p_ordernum order_num,round(p_score) score
           from dual
        ) p
     on (s.buid = p.buid
    and  s.order_num = p.order_num
        )
  when matched
  then update
          set velocity_score = decode(p_scoretype,'V',p.score,velocity_score),
              negative_score = decode(p_scoretype,'N',p.score,negative_score),
              trend_score = decode(p_scoretype,'T',p.score,trend_score),
              vendor_combined_score = decode(p_scoretype,'X',p.score,vendor_combined_score),
              vendor_bt_score = decode(p_scoretype,'XB',p.score,vendor_bt_score),
              vendor_st_score = decode(p_scoretype,'XS',p.score,vendor_st_score)
  when not matched
  then insert (buid,order_num,received_date_time,
               velocity_score,negative_score,trend_score,
               vendor_combined_score,vendor_bt_score,vendor_st_score
              )
       values (p.buid,p.order_num,sysdate,
               decode(p_scoretype,'V',p.score),decode(p_scoretype,'N',p.score),decode(p_scoretype,'T',p.score),
               decode(p_scoretype,'X',p.score),decode(p_scoretype,'XB',p.score),decode(p_scoretype,'XS',p.score)
              );
merge into score_filter_apply s
  using (select *
           from score
        ) p
     on (s.buid = p.buid
    and  s.order_num = p.order_num
    and  s.buid = p_buid
    and  s.order_num = p_ordernum
        )
  when matched
  then update
          set velocity_score = p.velocity_score,
              negative_score = p.negative_score,
              trend_score = p.trend_score,
              vendor_combined_score = p.vendor_combined_score,
              vendor_bt_score = p.vendor_bt_score,
              vendor_st_score = p.vendor_st_score
  when not matched
  then insert (buid,order_num,received_date_time)
       values (p.buid,p.order_num,sysdate);
end setscores1;
/


Please help me.
Thanks.
Re: merge is not working not properly [message #582902 is a reply to message #582898] Tue, 23 April 2013 05:24 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Existed record has to be updated in both the SCORE and score_filter_apply tables.


Updated in which way?
I'm not interested in code that does not work, I'm asking for the specifications.

Regards
Michel
Re: merge is not working not properly [message #582906 is a reply to message #582902] Tue, 23 April 2013 05:41 Go to previous messageGo to next message
ajaykumarkona
Messages: 399
Registered: August 2010
Senior Member
Update these values in both the tables.

velocity_score
negative_score
trend_score

Thanks.
Re: merge is not working not properly [message #582917 is a reply to message #582906] Tue, 23 April 2013 06:25 Go to previous messageGo to next message
Michel Cadot
Messages: 58590
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Do you REALLY think that your post is a correct specification?

Make some effort to help us to help you otherwise we think you don't really care to get help and it is not worth we waste our time with it.

Regards
Michel
Re: merge is not working not properly [message #583102 is a reply to message #582898] Thu, 25 April 2013 16:40 Go to previous message
Barbara Boehmer
Messages: 7937
Registered: November 2002
Location: California, USA
Senior Member
In your second merge statement in your procedure, change this:

merge into score_filter_apply s
  using (select *
           from score
        ) p
     on (s.buid = p.buid
    and  s.order_num = p.order_num
    and  s.buid = p_buid
    and  s.order_num = p_ordernum
        )


to this:

merge into score_filter_apply s
  using (select *
           from score
          where buid = p_buid
            and order_num = p_ordernum
        ) p
     on (s.buid = p.buid
    and  s.order_num = p.order_num
        )

[Updated on: Thu, 25 April 2013 16:41]

Report message to a moderator

Previous Topic: Complex batch allocation logic + summary of alternate rows [2 merged by jd]
Next Topic: DBMS_SQL.describe_columns2-session disconnect
Goto Forum:
  


Current Time: Sat Jul 26 12:28:21 CDT 2014

Total time taken to generate the page: 0.04930 seconds