Comments for MERGE Statement Enhancements in Oracle Database 10g
K P Ratnaker said...This Merge SQL Statement is very userfull in ORACLE 10g
Muthu said...explained very much clear. Useful too..
Wasif Hassan said...Really an nice addition.....specially the 'DELETE WHERE'
Murali Namburi said...Explained well. Merge statement in 10g reduces lot of coding effort.
Piyush Sachan said...Merge is got way to convert dual functionallity (insert/Update) into one sql statement. Performance wise also this is exceptionally good.
Jordi Just said...Sigo pensando que el perrino no funciona
martinka said...i found in 10g documentation this sentence
Use the MERGE statement to select rows from one or more sources for update or insertion into one or more tables
I would like to user MERGE statement into two tables ( if matched update table1, if not matched insert into table2), however i'm desperate, since i could not find any example on it. As cited above documentation clearly says it is possible.
please have you, any suggestions how to perform such MERGE ?
grace said...Very useful addition with DELETE WHERE Clause....
One guestion: delete can be on test1 a
table with WHERE Condition specified as stressed below? Is the following statement correct?
MERGE INTO test1 a
USING all_objects b
ON (a.object_id = b.object_id)
WHEN MATCHED THEN
UPDATE SET a.status = b.status
WHERE b.status != 'VALID'
DELETE WHERE (a.date = b.date)
BioBug said...FYI, Merge Statements with bind variabled don't currently work when called over a database link.
martinka said...to my previous comment: i found out , this is a bug in 10g documentation and is corrected in documentation for 11g. so unfortunatelly it is not possible to performe merge on two target tables.
Fernando said...If you want to make an update to table_1 when matched and an update to table_2 when not matched I think you can use an IF sentence...
I'm gonna try it and let you know
Ankur Kumar said...Thanks for sharing this wonderful article.
Akshay sharma said...Great explanation but the third hyperlink in the end which says merge(documentation) redirect to LOCK TABLES page not on merge documentation.
Thanks for the heads-up. I've fixed the link now. This manual uses function numbers, not names. When they add in a new page, all the others get moved along. The page used to be called "statements_9016.htm" and now it's called "statements_9017.htm". :)
Ashish said...Let us try:
MERGE INTO destination d
USING source s
ON (s.id = d.id)
WHEN MATCHED THEN
UPDATE SET d.description = 'Updated' WHERE s.status = 20
DELETE WHERE s.status = 10;
In this example, only 3 records are merged and nothing gets deleted from destination!!!
Does that surprise you?
The only rows being touched are those with status=20, so none of the touched rows have status=10, hence no deletes. You've indirectly added a new match clause for updates via the WHERE.
This looks reasonable to me.
Ashish said...Yes, the behavior of DELETE in this case definitely surprises me!
This means that the WHERE clause in the DELETE statement, also implicitly inherits all of the WHERE clauses in the UPDATE statement as well. I would have expected the UPDATE and DELETE statements to work independently of each other, over any record that MATCH the ON clause. I would expect 3recs updated and 2recs deleted here.
Ashish said...In the article, under the DELETE Clause, it says:
"Only those rows in the destination table that match both the ON clause and the DELETE WHERE are deleted."
But looks like for the DELETE Clause to work on a row, it needs to satisfy 3 things:
1. The row should match the ON clause.
2. The row should match the DELETE WHERE (if any)
3. The row should also match the UPDATE WHERE (if any)
As I said the last time you commented, it only effects the rows touched by the update. If you add a WHERE clause to the update, you are implicitly altering the match.
I'll put a comment about it if it makes you happy.
Ashish said...Really appreciate your feedback and changes!
Your assertion that adding a WHERE clause to the UPDATE implicitly alters the "MATCHED" records, would also imply that any record that does not satisfy the UPDATE WHERE, should be considered as "NOT MATCHED" and thus get INSERTED, but it doesnt do that. Hence, I was only considering the ON clause to decide what is MATCHED and what is NOT MATCHED.
I did not say they were added to the ON. I said we can think of them being an additional match, meaning in the context of the delete. In the additional wording I just added I accidentally said "matched", when I meant "touched", as shown in all the previous comments. Meaning only those rows touched by the update are available for the delete.
DO NOT ask technical questions here! They will be deleted!
These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!
If you post personal information (name, email address etc.) you are agreeing to them being stored and displayed. Feel free to remain anonymous.