Re: select/insert/delete
Date: Tue, 7 Jun 2011 20:49:48 -0700
Message-ID: <BANLkTikdmTaiB9YKnEk8n0=AXiqZExHQrA_at_mail.gmail.com>
Hi Robert,
not really seeing how that accomplishes a DELETE.
Again, what I am attempting to accomplish is to COPY some records from table A into table B and then DELETE those records from table A. I was looking for a single SQL statement that does not use functions or triggers. I don't believe it is possible, but I'm just checking with the group.
Regards,
Mike
On Tue, Jun 7, 2011 at 6:40 PM, Robert Freeman <robertgfreeman_at_yahoo.com>wrote:
> Something like:
>
> MERGE INTO ...
> USING (
> SELECT ...
> UNION
> SELECT ...
> )
> ON (...)
> WHEN MATCHED THEN UPDATE ...
> WHEN NOT MATCHED THEN INSERT ...
>
>
>
> 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:* Michael Moore <michaeljmoore_at_gmail.com>
> *To:* david_at_databasesecurity.com
> *Cc:* oracle-l_at_freelists.org
> *Sent:* Tue, June 7, 2011 6:36:52 PM
> *Subject:* Re: select/insert/delete
>
> I guess technically those don't involve a context switch however they're
> not exactly the 'pure sql' solution I was half hoping for. However, you got
> me thinking about the the trigger's performance if a do a bulk insert. Hmmm,
> I'll have to do some testing,
>
> Thanks,
> Mike
>
> On Tue, Jun 7, 2011 at 4:52 PM, David Litchfield <
> david_at_databasesecurity.com> wrote:
>
>> On 08/06/2011 00:47, David Litchfield wrote:
>>
>>> On 08/06/2011 00:12, Michael Moore wrote:
>>>
>>>> I think the answer to this is NO, but just to be sure,
>>>>
>>>> Is there a single SQL statement that can:
>>>>
>>>> 1) SELECT row-X from table A
>>>> 2) INSERT row-X into table B
>>>> 3) DELETE row-X from table A
>>>>
>>>> It would be nice to be able to do this without a context switch.
>>>>
>>> Yes you can... but it requires a bit of trickery (a function that'll
>>> execute arbitrary sql)... Follow the example below...
>>>
>>
>> Or you could use a trigger to fire after an insert on table_b to delete to
>> data in table_a... Which is probably the more sane way of doing it, lol :)
>>
>> HTH,
>> David
>>
>> --
>> http://www.freelists.org/webpage/oracle-l
>>
>>
>>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Jun 07 2011 - 22:49:48 CDT