Re: select/insert/delete

From: Michael Moore <michaeljmoore_at_gmail.com>
Date: Wed, 8 Jun 2011 13:14:23 -0700
Message-ID: <BANLkTinxdpd7QSzrDJ2OHtjG3iTyTbhrRw_at_mail.gmail.com>

Looks like the DELETE RETURNING BULK COLLECT into would get me 90% there. Too bad I can't "RETURNING" into a table.

Mike

On Wed, Jun 8, 2011 at 11:33 AM, Adam Musch <ahmusch_at_gmail.com> wrote:

> 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_at_gmail.com>
> 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_at_gmail.com
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Jun 08 2011 - 15:14:23 CDT