Link to home
Start Free TrialLog in
Avatar of ptuijtjens
ptuijtjens

asked on

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

   
Avatar of ptuijtjens
ptuijtjens

ASKER

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                    
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

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.
     
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
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.
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!
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...
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 ...   ;-)  
> Oh well, maybe they 'll get it right in 12g or 13g ...   ;-)  
hopefully, and probably :-)
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,