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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
Maybe I should have left the decode example off, it is a bit of an esoteric situation. Glad my explanation could help though.
ASKER
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!
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...
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...
ASKER
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 ... ;-)
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 :-)
hopefully, and probably :-)
ASKER
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,
But if anyone from Oracle is reading this: == YOU KNOW I 'M RIGHT !!! ==
Again, thanks for all the help,
ASKER
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