Re: Merge two tables with UPDATE statement

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 - 11:32:37 CDT