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