Merge two tables with UPDATE statement

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 - 10:48:39 CDT