Re: Merge command - Delete - Part Two...
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: info@xxxxxxxxxxxxxxxxxxxxx, oracle-l@xxxxxxxxxxxxx
- Date: Sat, 13 Jun 2009 12:23:04 -0700 (PDT)
Yep, that was it. Meant to post an update but got sidetracked....
Thanks for the reply..... Think I'm gonna post a blog bit about this.... :-)
RF
Robert G. Freeman
Oracle ACE
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide
(Sybex)
Oracle Database 11g New Features (Oracle Press)
Portable DBA: Oracle (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Oracle9i RMAN Backup and Recovery (Oracle Press)
Oracle9i New Features (Oracle Press)
Other various titles out of print now...
Blog: http://robertgfreeman.blogspot.com
The LDS Church is looking for DBA's. You do have to be a Church member in
good standing. A lot of kind people write me, concerned I may be breaking
the law by saying you have to be a Church member. It's legal I promise! :-)
http://pages.sssnet.com/messndal/church/parachurch.pdf
----- Original Message ----
From: Randolf Geist <info@xxxxxxxxxxxxxxxxxxxxx>
To: oracle-l@xxxxxxxxxxxxx
Sent: Saturday, June 13, 2009 1:14:11 PM
Subject: Re: Merge command - Delete - Part Two...
Robert,
I don't think this is a bug, but a "feature" and seems to behave as documented.
As documented here:
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/statements_9016.htm#i2081030
"Specify the DELETE where_clause to clean up data in a table while populating
or updating it. The only rows affected by this clause are those rows in the
destination table that are updated by the merge operation."
Your example does not perform the DELETE operation because of the WHERE-clause
specified for the UPDATE.
You can only delete rows with this clause when they get updated, but your
"where status = 'INVALID'" predicate of the UPDATE prevents this, and therefore
the subsequent delete is not evaluated for this row. Removing this
"where"-clause from the UPDATE part will have the MERGE command remove the row.
Although I don't think that this answers your original question.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
> Follow-up question.... Has anyone had a problem with using the merge command
> and the delete option. I've been working on a real simple test case and this
> is
> looking like a bug.... feedback? This is running in 11.1.0.7. Is there
> something wrong in my merge command or am I just missing some key bit of
> understanding about merge and the delete option somewhere?
___________________________________________________________________
WEB.DE FreeDSL Komplettanschluss mit DSL 6.000 Flatrate und
Telefonanschluss für 17,95 Euro/mtl.! http://produkte.web.de/go/02/
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
