Re: select/insert/delete

  • From: Michael Moore <michaeljmoore@xxxxxxxxx>
  • To: Adam Musch <ahmusch@xxxxxxxxx>
  • Date: Wed, 8 Jun 2011 13:14:23 -0700

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@xxxxxxxxx> 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@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
>

Other related posts: