Re: select/insert/delete
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: Robert Freeman <robertgfreeman@xxxxxxxxx>
- Date: Wed, 8 Jun 2011 15:03:59 -0700
Hi Robert,
You might have missed my response to your previous response. Here it is
again so you don't need to hunt for it.
-------------------------------------------------------------------
Hi Robert,
"... I was looking for a single SQL statement that does not use functions or
triggers."
The function idea might be doable, but really, my intent on the original
post was to determined if there was some feature of SQL that I missed.
My original assertion still seems to be true, namely, 'You can't MOVE data
from table A to table B with a single PURE SQL statement.
By MOVE, I mean that the data no longer exists in table A.
By PURE SQL, I mean no use of PL/SQL in any capacity.
-----------------------------------------------------------------------------
Regards,
Mike
On Wed, Jun 8, 2011 at 2:59 PM, Robert Freeman <robertgfreeman@xxxxxxxxx>wrote:
> 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:
