Re: SQLPLUS vs PL/SQL question

From: Rohrbacher, Boris <rohbo_at_sbox.tu-graz.ac.at>
Date: Tue, 23 Jun 1998 21:59:20 +0200
Message-ID: <35900918.43DA7C40@sbox.tu-graz.ac.at>

Hi Iancrozier

> I need to change the value in one column of a table by adding the value of a
> column in a different table to it and putting the result back into the original
> column of the first table i.e. I want
> column 1 of table 1 to equal column 1 of table 1 plus column 2 of table 2.
> I know how to do it in PL/SQL with variables etc, but is there a way in SQLPLUS
> to achieve the same result?
>

You didn't say if you can join table2 and table1. i.e. if there are related in any way.

 First solution where you can join table2 and table1 :

 update table1 T1 set col1 = ( select sT1.col1 + sT2.col2 from table1 sT1, table2 sT2 where sT1.rowid = T1.rowid and sT1.joinCol = sT2.joincol )  where ..... any where statement for not to update any row in table1 if this is not intended.

 It's using a correlated update, i.e. subselect is executed for any row that is retrieved for update and. Its tied to it with sT1.rowid = T1.rowid.  Take care that the join of table1 and table2 returns only one row. Otherwise you will get "subquery returns more than one row"

 Second solution if can't join table2 and table1

update table1 T1
 set col1 = ( select X.col1 + Y.col2

from                                                         -- 1*
                      (  select  rowid,   col1,  'x' joincol   from   table1 )
X,                        --  2*
                      (  select  col2,  'x' joincol   from   table2  where rownum <
2 ) Y,     --  3*
                       where  X.rowid =
T1.rowid                                                              -- 4*
                         and  X.joincol =
Y.joincol                                                              -- 5*
                   )

where ..... any where statement for not to update any row in table1 if this is not intended.

*1  sum col1 and col2 from from listen subquery
*2  from listen subquery for table 1 :  If you have
*3  from listen subquery for table 2  : suppress multiple lines by rowlimit or by

other where clause
*4 suppress alle rows from table1 except the one you are working on currently *5 and use the pseudo join to join subquery tables

HTH
 Regards Robo

Received on Tue Jun 23 1998 - 14:59:20 CDT