Re: update problem

Here is the updateable subselect method. Note that the pkey of x1 and x2 must be col "a" in this case.

         A B
---------- ----------

         1        101
         2        102
         3        103

SQL> select * from x2;

         A B
---------- ----------

         3        503
         2        502
         1        501

SQL> desc x1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 A                                         NOT NULL NUMBER(38)
 B                                                  NUMBER(38)

SQL> desc x2;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------------
----
 A                                         NOT NULL NUMBER(38)
 B                                                  NUMBER(38)

SQL> update (select x1.b oldval, x2.b newval from x1, x2 where x1.a = x2.a) set oldval = newval;

3 rows updated.

SQL> select * from x1;

         A B
---------- ----------

         1        501
         2        502
         3        503

SQL>

HTH,
Tom Best

"Tom Best" <oracle_person_at_yahoo.com> wrote in message news:ahpdg7$k5v$1_at_news.bentley.com...
> Marc:
>
> Do this:
>
> update TableA set ColC = (select col2 from TableB where col1 =
> TableA.colB)
>
> I assume you wanted to set ColC to the values in col2. If I'm wrong,
 change
> the selected column.
>
> There is also a way to do it by updating the subselect (in 9i only, I
> believe). Take a look at updateable views in the doc.
>
> HTH,
> Tom Best
>
> "M" <morc66_at_hotmail.com> wrote in message
> news:vWV%8.23147$uV1.1216367_at_zwoll1.home.nl...
> > Hi all,
> >
> > I have this, probably very easy to solve, problem to solve:
> >
> > I am trying to update a talble using entries in another table.
> >
> > TableA
> > colA colB colC
> > 1 A 1
> > 2 B x
> > 3 X 13
> >
> > TableB
> > col1 col2
> > Z 123
> > H 223
> > B 323
> >
> > now I want to update tableA ColC where tableAcolB = tableBcol1, in fact
> > updating all rows from tableA which have entries in table B.
> >
> > It seems to be unpossible using a subselect in a update... or am I
 wrong?
> > (yep,... newbie)
> >
> > Marc
> >
> >
>
>
Received on Thu Jul 25 2002 - 20:58:43 CEST