Re: Merge two tables with UPDATE statement

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 Tue Apr 03 2001 - 16:30:31 CDT