Re: Performing a JOIN in an Update Statement (HELP)

You could also do this, if Table2 is key-preserved on colA.

--update <table> set [colX] = [colY]

update (select

             t1.colB t1ColB, t1.colC t1ColC, 
             t2.colB t2ColB, t2.colC t2ColC
        from table1 t1, table t2
        where t1.colA = t2. colA) mytable

Prince..

postbus_at_sybrandb.demon.nl (Sybrand Bakker) wrote in message news:<a20d28ee.0205222336.5ed9f695_at_posting.google.com>...
> kpkeller_at_linuxmail.org (KPK) wrote in message news:<6f5ea0d.0205220629.30ffd4d8_at_posting.google.com>...
> > Hello All:
> >
> > I am trying to perform an INNER JOIN in an update statement in Oracle 8.
> > I can't seem to get the format just right.
> >
> > Can anyone help?
> >
> > Here are the formats I've tried but failed at getting to work:
> >
> > Example 1:
> > update Table1
> > set colB = table2.colB,
> > colC = table2.colC
> > From Table2
> > Where Table1.ColA = Table2.ColA
> >
> >
> > Example 2:
> > update Table1
> > set colB = table2.colB,
> > colC = table2.colC
> > Where Table1.ColA IN (SELECT Table2.ColA From Table2)
> >
> > No good. Can anyone help?
> > As you can see, I'm updating table1 with some records from table2.
>
>
> update table1
> set (colb, colc) =
> (select table2.colb
> , table2.colc
> from table1 where table1.cola = table2.cola)
> where
> exists
> (select 'x'
> from table1
> where table1.cola=table2.cola)
>
> Hth
>
> Sybrand Bakker
> Senior Oracle DBA
Received on Fri May 24 2002 - 21:38:54 CEST