Re: select/insert/delete

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
--
http://www.freelists.org/webpage/oracle-l

Other related posts: