Update data in one table from another

From: Sandy Murdock <jmurdock_at_dialoguewizard.com>
Date: 15 Jan 2002 16:57:29 -0800
Message-ID: <ac4a5663.0201151657.36ff5328@posting.google.com>

I found a message from Thomas Kyte that details updating a field in one table with data from another.

It looked like this:

scott_at_ORA734.WORLD> update name

  2     set columnName = ( select value
  3                          from lookup
  4                         where lookup.keyname = name.keyname
  5                           and otherColumn = :other_value )
  6   where exists ( select value
  7                    from lookup
  8                   where lookup.keyname = name.keyname
  9                     and otherColumn = :other_value )

 10 /

I have tried this out and it works fine, when I need to update one field. (It's quite fast)

I need to update almost every field in the table.

We have a table that contains system users, this table will be updated nightly from another system which is not compatible with oracle (can't just connect).

We are importing the data (sqlldr) and doing the updates using a cursor / fetch (It is brutally slow) It would run much faster (I would think) if I change the procedure to work as above, but updating multiple fields.

What is the best way to do this? Do I write one update query per field?

All suggestions will be considered - I hope to be able to figure out the best one and apply it.

Thanks in advance.

Sandy Murdock Received on Tue Jan 15 2002 - 18:57:29 CST