Re: select/insert/delete
- From: David Litchfield <david@xxxxxxxxxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Wed, 08 Jun 2011 00:47:05 +0100
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...
SQL> create or replace function arbsql (x varchar2) return number is
2 begin
3 execute immediate x;
4 return 1;
5 end;
6 /
Function created.
SQL> create table table_a (x number, y varchar2(30));
Table created.
SQL> insert into table_a (x,y) values (1,'test1');
1 row created.
SQL> insert into table_a (x,y) values (2,'test2');
1 row created.
SQL> insert into table_a (x,y) values (3,'test3');
1 row created.
SQL> commit;
Commit complete.
SQL> create table table_b (x number, y varchar2(30));
Table created.
SQL> insert into table_b (x,y) values (0,'');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from table_a;
X Y
---------- ------------------------------
1 test1
2 test2
3 test3
SQL> select * from table_b;
X Y
---------- ------------------------------
0
SQL> delete from table_a where x = 2 + arbsql('insert into table_b (x,y)
values ((s
elect x from table_a where x = 2), (select y from table_a where x =
2))') - 1;
1 row deleted.
SQL> select * from table_a;
X Y
---------- ------------------------------
1 test1
3 test3
SQL> select * from table_b;
X Y
---------- ------------------------------
0
2 test2
2 test2
HTH,
David
--
//www.freelists.org/webpage/oracle-l
Other related posts:
