Re: select/insert/delete
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: Michael Moore <michaeljmoore@xxxxxxxxx>
- Date: Tue, 7 Jun 2011 23:19:59 -0700 (PDT)
Sorry, missed that little bit of detail there. :).,.....
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@xxxxxxxxx>
To: Robert Freeman <robertgfreeman@xxxxxxxxx>
Cc: david@xxxxxxxxxxxxxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Tue, June 7, 2011 9:49:48 PM
Subject: Re: select/insert/delete
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@xxxxxxxxx> 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@xxxxxxxxx>
>To: david@xxxxxxxxxxxxxxxxxxxx
>Cc: oracle-l@xxxxxxxxxxxxx
>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@xxxxxxxxxxxxxxxxxxxx>
>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
>>
>>--
>>//www.freelists.org/webpage/oracle-l
>>
>>
>>
>
Other related posts:
