Re: Merge two tables with UPDATE statement

Try this:

UPDATE (SELECT old.f1 old_f1, new.f1 new_f1, old.f2 old_f2, new.f2 new_f2

        FROM     t1 old, t2 new
        WHERE    (old.id = new.id)) a
   SET a.old_f1 = a.new_f1,
       a.old_f2 = a.new_f2

"Tom Bjerre" <tom_at_bjerre.cx> wrote in message news:9adfdl$3rh$1_at_news.cybercity.dk...
> This will only work if the two tables contains excactly the same primary
> keys. This is not the case since t2 normally only holds a very small
 subset
> of the records in t1 that is to be updated.
>
> Regards, Tom Bjerre.
>
> "TurkBear" <noone_at_nowhere.com> wrote in message
> news:fcujctgute49apcauoeir7kcjtt6hcp6ia_at_4ax.com...
> >
> > Try this:
> >
> > Update t1 set (f1,f2) = (select f1,f2 from t2 where t1.pk = t2.pk);
> >
> > ---------------
> >
> > "Tom Bjerre" <tb_at_dit.dk> wrote:
> >
> > >I have two tables t1 and t2 with the same structure: an unique primary
 key
> > >and some arbitrary fields. The tables look something like:
> > >
> > > pk number
> > > f1 varchar2(10)
> > > f2 varchar2(10)
> > > f3 varchar2(10)
> > >
> > >I have some "new" data in the records in t2 that I would like to update
 the
> > >corresponding rows in t1 with.
> > >
> > >On a Microsoft SQL server I could use a SQL statement like:
> > >
> > >UPDATE t1
> > > SET t1.f1 = new.f1, t1.f2 = new.f2
> > > FROM t1, t2 new
> > > WHERE new.pk = t1.pk;
> > >
> > >But this doesn't work with Oracle (8.1.7). Instead I have come up with
> > >something like:
> > >
> > >UPDATE t1 old
> > > SET (f1, f2) =
> > > (SELECT f1, f2 FROM t2 new WHERE new.pk = old.pk)
> > > WHERE old.pk IN (SELECT pk FROM t2);
> > >
> > >The problem here is that the Oracle version performs very poorly (it's
> > >obvious why), whereas the MS SQL server version works like a charm.
> > >
> > >There must be something I'm missing here - can anybody please enlithen
 me.
> > >
> > >How do you merge the contents of two tables on Oracle.
> > >
> > >Regards,
> > >
> > >Tom Bjerre
> > >
> > >
> > >
> > >
> > >
> >
>
>
Received on Wed Apr 04 2001 - 02:55:41 CDT