Re: select/insert/delete

From: Adam Musch <ahmusch_at_gmail.com>
Date: Wed, 8 Jun 2011 13:33:39 -0500
Message-ID: <BANLkTikb+2LsjgkQ63nrjR0WfkbV+n9AZQ_at_mail.gmail.com>

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 - 13:33:39 CDT