Re: select/insert/delete
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: ahmusch@xxxxxxxxx, knecht.stefan@xxxxxxxxx
- Date: Wed, 8 Jun 2011 14:59:12 -0700 (PDT)
See my earlier response. You can build a function that you call in the DELETE
that will remove the rows in question.
Robert G. Freeman
Master Principal Consultant, Oracle Corporation, Oracle ACE
Author of various books on RMAN, New Features and this shorter signature line.
Blog: http://robertgfreeman.blogspot.com
Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It is just
the
opinion of one Oracle employee. I can be wrong, have been wrong in the past and
will be wrong in the future. If your problem is a critical production problem,
you should always contact Oracle support for assistance. Statements in this
email in no way represent Oracle Corporation or any subsidiaries and reflect
only the opinion of the author of this email.
________________________________
From: Adam Musch <ahmusch@xxxxxxxxx>
To: knecht.stefan@xxxxxxxxx
Cc: michaeljmoore@xxxxxxxxx; ORACLE-L <oracle-l@xxxxxxxxxxxxx>
Sent: Wed, June 8, 2011 12:33:39 PM
Subject: Re: select/insert/delete
The WHEN MATCHED THEN UPDATE ... DELETE only permits deleting from the
target table, and I don't think it can you can build a key-preserved
view as the target of the join, especially when the target table
doesn't have the matching rows.
After reading the OP's most recent post post, I suspect that the only
practical choices are to:
- Build a PL/SQL collections of records for manipulation, validation
and migration:
- Use a global temporary table as a staging where one does as much
manipulation and validation at the SQL level. This should mitigate
some of the concurrency issues, as you're working with a copy of the
data.
- Exploit the LOG ERRORS DML clause to capture the records which fail
referential integrity validation. If you're not using RI, well, that
solution will be less effective.
On Wed, Jun 8, 2011 at 3:13 AM, Stefan Knecht <knecht.stefan@xxxxxxxxx> wrote:
> I saw merge was suggested, but without mentioning that merge actually can do
> deletes as well in recent versions...
> I haven't tested it, but what if you merge into a view that joins you
> table_a and table_b together. Then it might fulfill your 3 statements in 1
> go.
> Stefan
>
--
Adam Musch
ahmusch@xxxxxxxxx
--
//www.freelists.org/webpage/oracle-l
Other related posts:
