Re: select/insert/delete
- From: Michael Moore <michaeljmoore@xxxxxxxxx>
- To: Robert Freeman <robertgfreeman@xxxxxxxxx>
- Date: Wed, 8 Jun 2011 09:36:24 -0700
Hi Robert,
"... I was looking for a single SQL statement that does not use functions or
triggers."
The function idea might be doable, but really, my intent on the original
post was to determined if there was some feature of SQL that I missed.
My original assertion still seems to be true, namely, 'You can't MOVE data
from table A to table B with a single PURE SQL statement.
By MOVE, I mean that the data no longer exists in table A.
By PURE SQL, I mean no use of PL/SQL in any capacity.
Thanks for spending your time on this. :-)
Regards,
Mike
On Tue, Jun 7, 2011 at 11:46 PM, Robert Freeman <robertgfreeman@xxxxxxxxx>wrote:
> Ok.... How about something like this.... it's a quick and dirty... I'd
> throw in exception clauses and so on, but in the little testing I did it
> seemed to work. Basically I created a function called in the WHERE clause of
> the MERGE function. It uses the (undefined) pk of the from table to delete
> the row. I tried to use the ROWID but that generated an ORA error.....
>
> drop table insert_into;
> drop table insert_from;
>
> create table insert_into (id number, id_two number);
> create table insert_from (id number, id_two number);
>
> insert into insert_from values (1,2);
> insert into insert_from values (2,3);
> commit;
>
>
> MERGE INTO insert_into D
> USING (SELECT id, id_two, rowid FROM insert_from) S
> ON (D.id = S.id)
> WHEN MATCHED THEN UPDATE SET D.id_two = S.id_two
> WHEN NOT MATCHED THEN INSERT (D.id, D.id_two) VALUES (1,S.id)
> WHERE (fu_delete_value('TEST','INSERT_FROM',s.id)=0);
>
>
> create or replace function fu_delete_value (p_owner varchar2, p_table_name
> varchar2, p_rowid number)
> return number
> as
> v_sql varchar2(2000);
> begin
> v_sql:='delete from '||p_owner||'.'||p_table_name||' where
> id='||p_rowid;
> execute immediate v_sql;
> return 0;
> end;
>
>
>
> 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:
