Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Oracle MERGE statement (Oracle10)

Posted on 2007-10-15
12
Medium Priority
?
2,185 Views
Last Modified: 2012-06-27
Hello, I'm quite new at working with Oracle, but I thought I'd try my hand at the  wonderful MERGE statement. Now I've got it to work, well, almost, because I can't get it to delete a row. I'll paste my coding below.
I am trying to delete the ArtNo = 2 row, but somehow  the row in myMutations with Artno = 2 is completely ignored. Any suggestions anyone ?

-- Create a stocktable
DROP TABLE   myStock;
CREATE TABLE myStock (
   ArtNo        NUMBER(5),
   Description  VARCHAR2(50),
   Stock        NUMBER(5));
INSERT INTO myStock VALUES (1, 'Screw 2.0x15', 5000);
INSERT INTO myStock VALUES (2, 'Screw 2.5x15', 5000);
INSERT INTO myStock VALUES (3, 'Screw 3.0x20', 5000);
INSERT INTO myStock VALUES (4, 'Screw 3.5x20', 5000);
INSERT INTO myStock VALUES (5, 'Screw 3.5x25', 5000);

-- create a Mutations tabel
DROP TABLE   myMutations;
CREATE TABLE myMutations (
   ArtNo        NUMBER(5),
   Description  VARCHAR2(50),
   Qty          NUMBER(5),
   MutType      VARCHAR2(3));
INSERT INTO myMutations  VALUES (1, '', -10, 'CHG');
INSERT INTO myMutations  VALUES (2, '', 0, 'DEL');
INSERT INTO myMutations  VALUES (3, 'Screw 2.0x20 BRASS', 5, 'CHG');
INSERT INTO myMutations  VALUES (4, 'Screw 3.5x20 INOX', 0, 'CHG');
INSERT INTO myMutations  VALUES (9, 'Small nails', 2000, 'NEW');

COMMIT;

MERGE INTO myStock s USING myMutations m ON (s.ArtNo = m.ArtNo)
WHEN MATCHED THEN
   UPDATE
   SET s.Description = coalesce(m.Description, s.Description),
       s.Stock       = s.Stock + m.Qty
   WHERE m.MutType = 'CHG'            
   DELETE WHERE m.MutType = 'DEL'
WHEN NOT MATCHED THEN
   INSERT (s.ArtNo, s.Description, s.Stock) VALUES (m.ArtNo, m.Description, m.Qty)
   WHERE m.MutType = 'NEW';

COMMIT;
SELECT * FROM myMutations;

select * from myStock;
--------------------------------------------------------------------------------------------------------------
And here is the result:
-------------------------------------------------------------------------------------------------------------
DROP TABLE  succeeded.

CREATE TABLE succeeded.

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted


DROP TABLE  succeeded.

CREATE TABLE succeeded.

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted

1 rows inserted

COMMIT succeeded.

4 rows merged

COMMIT succeeded.

ARTNO                  DESCRIPTION                                        QTY                    MUTTYPE
---------------------- -------------------------------------------------- ---------------------- -------
1                                                                         -10                    CHG    
2                                                                         0                      DEL    
3                      Screw 2.0x20 BRASS                                 5                      CHG    
4                      Screw 3.5x20 INOX                                  0                      CHG    
9                      Small nails                                        2000                   NEW    

5 rows selected


ARTNO                  DESCRIPTION                                        STOCK                  
---------------------- -------------------------------------------------- ----------------------
1                      Screw 2.0x15                                       4990                  
2                      Screw 2.5x15                                       5000                  
3                      Screw 2.0x20 BRASS                                 5005                  
4                      Screw 3.5x20 INOX                                  5000                  
5                      Screw 3.5x25                                       5000                  
9                      Small nails                                        2000                  

6 rows selected

   
0
Comment
Question by:ptuijtjens
  • 5
  • 4
  • 2
  • +1
12 Comments
 

Author Comment

by:ptuijtjens
ID: 20079083
It seems to delete allright if I remove the WHERE clause for update:  
WHERE m.MutType = 'CHG'  but that 's no solid solution IMO:

ARTNO                  DESCRIPTION                                        STOCK                  
---------------------- -------------------------------------------------- ----------------------
1                      Screw 2.0x15                                       4990                  
3                      Screw 2.0x20 BRASS                                 5005                  
4                      Screw 3.5x20 INOX                                  5000                  
5                      Screw 3.5x25                                       5000                  
9                      Small nails                                        2000                    
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 800 total points
ID: 20079194
please try this (guessing, as I don't have oracle here to test):

MERGE INTO myStock s USING myMutations m ON (s.ArtNo = m.ArtNo)
WHEN MATCHED THEN
   UPDATE
   SET s.Description = coalesce(m.Description, s.Description),
       s.Stock       = s.Stock + m.Qty
   WHERE m.MutType IN ( 'CHG'  , 'DEL' )          
   DELETE WHERE m.MutType = 'DEL'
WHEN NOT MATCHED THEN
   INSERT (s.ArtNo, s.Description, s.Stock) VALUES (m.ArtNo, m.Description, m.Qty)
   WHERE m.MutType = 'NEW';
0
 
LVL 3

Expert Comment

by:prasanthi_k
ID: 20079197
Merge statement is only for update and insert (it is also called as UPSERT).
Its cool that even delete worked for you....

In Oracle every statement has to end with a ';', for it to recognize the next statement to execute.
In merge there can not be a ' ;' in between, that means we cannot get both update and delete get worked inside WHEN condition.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 200 total points
ID: 20079843
This must be 10g since you are doing a delete.  9i didn't support delete in a merge.

The trick you've got to watch for in delete's is that deletes are sub-clause to the WHEN MATCHED clause, or in other words, they are a sub-clause to the UPDATE portion of your merge.

So your delete must be on records that you are updating.

Thus....

WHERE m.MutType = 'CHG'            
   DELETE WHERE m.MutType = 'DEL'  

are contradictory statements.

So, something like angeliii has above might work, but if you have triggers that might do something based on changed values you need to do the update but with dummy steps for the deletes,
a=a, b=b  

something like this....

MERGE INTO mystock s
    USING mymutations m
    ON (s.artno = m.artno)
    WHEN MATCHED THEN
        UPDATE
           SET s.description =
                   DECODE(m.muttype,
                          'CHG', COALESCE(m.description, s.description),
                          s.description
                         ),
               s.stock = DECODE(m.muttype, 'CHG', s.stock + m.qty, s.stock)
            WHERE m.muttype IN('CHG', 'DEL')
        DELETE
            WHERE m.muttype = 'DEL'
    WHEN NOT MATCHED THEN
        INSERT(s.artno, s.description, s.stock)
        VALUES(m.artno, m.description, m.qty)
            WHERE m.muttype = 'NEW';

0
 

Author Comment

by:ptuijtjens
ID: 20083654

Thank you Angellll, that was very good. You did that by heart ? Respect !
Have to give some points to sdstuber though, for the clear explanation and the decode suggestion.
     
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20083685
Hi, I was only guessing, as I said, although I assumed exactly what sdstuber was explaining about the DELETE clause being a subclause of the WHEN MATCHED...

note, that I actually do think that I do not agree with the need of the decode, because when the delete (condition and effect) will apply, the update is "futile" anyhow ...

glad we could help
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 20084808
The decode wasn't a "need" for a every case, but ONLY if there were triggers that would do something when values changed.  So, if you didn't want a trigger to fire and update some other table (for instance).   The delete does happen after the update, so, like angeliii said, preserving or changing the values of the merged table itself is of no consequence in this example.

Maybe I should have left the decode example off, it is a bit of an esoteric situation.  Glad my explanation could help though.
0
 

Author Comment

by:ptuijtjens
ID: 20085313
Well, I was very happy with the DECODE suggestion, for it avoids a possible problem, and all programmers I know have heard of this guy named Murphy .....
It made the coding more robust and more suited for general use and future reference.
So, thanks again!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20087509
Well, I am sorry, but I have to disagree again...
the UPDATE clause, even with the decode value, will set the value to the same value, and still fire the trigger, no matter if you use decode or not...
at least that is what I learned and experience with triggers, it does not matter if you actually CHANGE a value, it is the pure fact that the row is selected by the where clause that will make it inside the trigger...
0
 

Author Comment

by:ptuijtjens
ID: 20091144
Angelll, you're right, triggers will fire.
 I should have thought of that, rcently used that little trick myself.

As I said before, I 'm new at  this, but it feels to me the logic of the MERGE statement itself is somehow flawed.
Why would one make one WHERE - clause cancel out the other one ?
I think it should have been designed so it would have worked as in my original posting.
That one makes sense to me.
 Oh well, maybe they 'll get it right in 12g or 13g ...   ;-)  
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20091169
> Oh well, maybe they 'll get it right in 12g or 13g ...   ;-)  
hopefully, and probably :-)
0
 

Author Comment

by:ptuijtjens
ID: 20091187
Well, maybe it is a bit presumptuous of me to say my MERGE is better than Oracle's MERGE.
But if anyone from Oracle is reading this:  == YOU KNOW I 'M RIGHT !!! ==

Again, thanks for all the help,

0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses
Course of the Month11 days, 7 hours left to enroll

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question