Re: Update data in one table from another

From: Sybrand Bakker <postbus_at_sybrandb.demon.nl>
Date: Wed, 16 Jan 2002 07:45:02 +0100
Message-ID: <e98a4u82ee3efrksgqj6qdmab0218a1p9v@4ax.com>

On 15 Jan 2002 16:57:29 -0800, jmurdock_at_dialoguewizard.com (Sandy Murdock) wrote:

>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

This syntax
set (col1,col2,... coln) = (select col1,col2, ... coln

                                        from ...

is completely allowed
Please verify the syntax of the update statement.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Jan 16 2002 - 00:45:02 CST