Oracle MERGE statement (Oracle10)

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

   
ptuijtjensAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ptuijtjensAuthor Commented:
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
 
prasanthi_kCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
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
 
ptuijtjensAuthor Commented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
sdstuberCommented:
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
 
ptuijtjensAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
ptuijtjensAuthor Commented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
> Oh well, maybe they 'll get it right in 12g or 13g ...   ;-)  
hopefully, and probably :-)
0
 
ptuijtjensAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.