Home » SQL & PL/SQL » SQL & PL/SQL » can we use MERGE statement in this scenario (oracle 10g)
can we use MERGE statement in this scenario [message #442022] Thu, 04 February 2010 07:48 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
SELECT SUM(VD.CREDITAMOUNT),
       SUM(VD.DEBITAMOUNT)
  FROM DOCUMENT VD
 WHERE VD.SER_NO = 1222;
 
 if(CreditAmount==DebitAmount) {
delete statment will happen here.
}else if(CreditAmount>DebitAmount) {
update statment will happen on certain columns with different conditions. 
}else{
update statment will happen on certain columns with different conditions.
}

intially we are executing one select statement by returning the values from the select statements we are doing certain
operations like as you see above if CreditAmount is equal to DebitAmount we are deleting one table .if it is greater than we are doing some update operation.normallly we do this kind of operations in normal applications.

so my question is instead of doing all these things Is there any oracle function like MERGE statement
depending upon the conditions it should do multiple update statements as well as delete statement.Is it possible in oracle?

[Updated on: Thu, 04 February 2010 07:50] by Moderator

Report message to a moderator

Re: can we use MERGE statement in this scenario [message #442023 is a reply to message #442022] Thu, 04 February 2010 07:54 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Depending on which table and rows you want to update/delete and what are the conditions it could be done in one MERGE statement.

Regards
Michel
Re: can we use MERGE statement in this scenario [message #442024 is a reply to message #442022] Thu, 04 February 2010 07:58 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,
We go for MERGE when there are 2 conditions IF and ELSE but not IFELSE repeatedly.

You can use MERGE as given in Eg below,
MERGE
 INTO  tgt
 USING src
 ON   (src.object_id = tgt.object_id)
 WHEN MATCHED
  THEN
  UPDATE
  SET tgt.object_name = LOWER(src.object_name)

 WHEN NOT MATCHED
 THEN
  DELETE
 WHERE tgt.object_type = 'TYPE'

[Updated on: Thu, 04 February 2010 08:01]

Report message to a moderator

Re: can we use MERGE statement in this scenario [message #442026 is a reply to message #442022] Thu, 04 February 2010 08:02 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
In my case update/delete on DOCUMENT table only.how the conditions will be used when we do update/delete statements in MERGE statement.
Re: can we use MERGE statement in this scenario [message #442028 is a reply to message #442024] Thu, 04 February 2010 08:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not a valid MERGE statement, please check the syntax.

Regards
Michel
Re: can we use MERGE statement in this scenario [message #442029 is a reply to message #442026] Thu, 04 February 2010 08:04 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
chaituu wrote on Thu, 04 February 2010 15:02
In my case update/delete on DOCUMENT table only.how the conditions will be used when we do update/delete statements in MERGE statement.

Using CASE.

Regards
Michel

Re: can we use MERGE statement in this scenario [message #442030 is a reply to message #442028] Thu, 04 February 2010 08:08 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Michel Cadot wrote on Thu, 04 February 2010 19:33
This is not a valid MERGE statement, please check the syntax.

Regards
Michel



Hi,

Could you please tell me the wrong thing in above MERGE statement.
Re: can we use MERGE statement in this scenario [message #442031 is a reply to message #442022] Thu, 04 February 2010 08:08 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
I didnt get an idea how to use CASE ?.can you give me some hint?
Re: can we use MERGE statement in this scenario [message #442032 is a reply to message #442031] Thu, 04 February 2010 08:10 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As soon as you provide a working Test case (create table and insert statements along with the result you want with these data) then we will work with your table and data and show you how to do it (if it is possible).

Regards
Michel

[Updated on: Thu, 04 February 2010 08:10]

Report message to a moderator

Re: can we use MERGE statement in this scenario [message #442034 is a reply to message #442032] Thu, 04 February 2010 08:21 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi,

Hope these will be usefull..
MERGE
 INTO  tgt
 USING src
 ON   (creditamount=debitamount)
 WHEN MATCHED
  THEN
  UPDATE
  SET ..... 

 WHEN NOT MATCHED
 THEN
   CASE WHEN creditamount>debitamount THEN
             DELETE
             WHERE <condition>....
        ELSE 
             DELETE
             WHERE <condition>....
   END


[Updated on: Thu, 04 February 2010 08:21]

Report message to a moderator

Re: can we use MERGE statement in this scenario [message #442036 is a reply to message #442034] Thu, 04 February 2010 08:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Still not a valid MERGE statement, please check the syntax in the documentation.

Regards
Michel
Re: can we use MERGE statement in this scenario [message #442037 is a reply to message #442036] Thu, 04 February 2010 08:24 Go to previous messageGo to next message
tejasvi_ss
Messages: 22
Registered: February 2010
Location: Bangalore, India
Junior Member
Hi ,

can u please correct me the wrong thing as i am unable to find it out. I saw documentation also.please suggest me
Re: can we use MERGE statement in this scenario [message #442039 is a reply to message #442037] Thu, 04 February 2010 08:31 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Did you see in the documentation that:
WHEN NOT MATCHED
 THEN
   CASE WHEN creditamount>debitamount THEN

is a valid syntax?
If so please send me a link to this.

Regards
Michel
Re: can we use MERGE statement in this scenario [message #442040 is a reply to message #442022] Thu, 04 February 2010 08:33 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
as you asked i am sending testcase.

create table DOCUMENT
(
  SER_NO       NUMBER,
  CREDITAMOUNT NUMBER,
  DEBITAMOUNT  NUMBER
);

insert into DOCUMENT (SER_NO, CREDITAMOUNT, DEBITAMOUNT)
values (1503, 0, 31326);
insert into DOCUMENT (SER_NO, CREDITAMOUNT, DEBITAMOUNT)
values (1323, 680.9, 0);
insert into DOCUMENT (SER_NO, CREDITAMOUNT, DEBITAMOUNT)
values (67776, 2000, 0);
insert into DOCUMENT (SER_NO, CREDITAMOUNT, DEBITAMOUNT)
values (568057, 350, 0);
insert into DOCUMENT (SER_NO, CREDITAMOUNT, DEBITAMOUNT)
values (198, 0, 8900);
commit;

--evrytime we will pass serail no to select statement and 
based on fetching CREDITAMOUNT and DEBITAMOUNT we will do 
below operations

SELECT SUM(VD.CREDITAMOUNT),
       SUM(VD.DEBITAMOUNT)
  FROM DOCUMENT VD
 WHERE VD.SER_NO = 1503;
 
if(CREDITAMOUNT=DEBITAMOUNT) then
DELETE FROM DOCUMENT VD  WHERE VD.SER_NO = 1503;
else  if(CREDITAMOUNT>DEBITAMOUNT) then
UPDATE DOCUMENT VD
  SET VD.DEBITAMOUNT = (CREDITAMOUNT-DEBITAMOUNT), VD.CREDITAMOUNT = 0
  WHERE VD.SER_NO =1503;
 else
 UPDATE DOCUMENT VD
  SET VD.DEBITAMOUNT = 0, VD.CREDITAMOUNT = (CREDITAMOUNT-DEBITAMOUNT)
  WHERE VD.SER_NO =1503;
end if;  

[Updated on: Thu, 04 February 2010 09:18] by Moderator

Report message to a moderator

Re: can we use MERGE statement in this scenario [message #442041 is a reply to message #442040] Thu, 04 February 2010 08:55 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
What is the problem with this statment;missing expression is coming?

SELECT VD1.SER_NO,
       SUM(VD1.CREDITAMOUNT) CREDITAMOUNT,
       SUM(VD1.DEBITAMOUNT) DEBITAMOUNT,
       CASE WHEN (SUM(VD1.CREDITAMOUNT) > SUM(VD1.DEBITAMOUNT)) THEN 
      (UPDATE DOCUMENT VD 
       SET VD.DEBITAMOUNT = (SUM(VD.CREDITAMOUNT) - SUM(VD.DEBITAMOUNT)), 
       VD.CREDITAMOUNT = 0
     WHERE VD.SER_NO = VD1.SER_NO
         
          ELSE '' 
            END)
 FROM DOCUMENT VD1
 WHERE VD1.SER_NO = 1503;
  

Re: can we use MERGE statement in this scenario [message #442046 is a reply to message #442022] Thu, 04 February 2010 09:41 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
That looks like an attempt to do a merge without using the merge keyword. You can't put updates in normal selects.
Re: can we use MERGE statement in this scenario [message #442052 is a reply to message #442022] Thu, 04 February 2010 09:54 Go to previous message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
A caution about MERGE. Merge is an ADVANCED feature of Oracle. All advanced features of Oracle suffer from one common characteristic: they do not always play well with each other.

In the case of MERGE you have to watch out for triggers. There are two types of triggers, table based triggers and view based instead-of-triggers.

Many people think MERGE is the Oracle incarnation of the UPSERT. If you don't understand UPSERT then look it up. The problem is that MERGE is not strictly speaking an UPSERT operation made manifest. To understand this, consider the following question: if you had table row level triggers on your table being MERGED, what triggers should fire? If MERGE is simply UPSERT then you would expect to see update row triggers fire or else insert row triggers fire. But the triggers that fire and anything but. To satisfy yourself, do the following test:

1) create a table
2) create one of each type of trigger (before/after insert/update/delete table level), (before/after insert/update/delete row level).
3) run your merge and see what triggers are fired


You will need some way of following trigger execution. I suggest an autonomous_transaction in each trigger that writes to a message table. In any event, the results will not be what you expect or at least will not resemble in any form what the corresponding traditional UPSERT logic would have caused to fire.

So if you are using table triggers you will want to rethink a decision to use MERGE.

If you use MERGE you will want to rethink use of TABLE triggers.

As far as instead-of-triggers goes, MERGE will not execute on a view with instead of triggers. It generate an error instead. Thus if your databse design strategies anticipate use of instead-of-triggers then you will want to stay away from MERGE.

In the end I avoid MERGE at all costs. I am too big a fan of instead-of-triggers as a design strategy to use any feature that would prohibit me from deploying instead-of-triggers in my systems. In code reviews I require developers to remove MREGE from their code. I freely admit that this is my choice and there will surely be some on this site that will disagree. I look forward to their response. In particular I would ask them which they think is more valuable, MERGE or INSTEAD-OF-TRIGGERS because you can't have both.

Good luck, Kevin
Previous Topic: strange ora-00942 in a procedure.
Next Topic: clob column
Goto Forum:
  


Current Time: Sun Sep 25 16:18:15 CDT 2016

Total time taken to generate the page: 0.07023 seconds