I'm looking for a way to update data in one table with data from
another table.

More specifically, if there are any records in the two tables which
have matching primary keys but differing data in at least one other
column, I need to update the values in the old table from the new
table.

I can't just do a blanket update of every record, because that would
result in a lot of unneccesary updates -- I only want to update
records which actually have different data.

I tried an UPDATE FROM statement, but Oracle doesn't support that.

I tried a MERGE INTO statement, but Oracle 9i requires action to be
taken both when the records match *and* when they don't. Bah!

Any suggestions?

gordon@panix.com wrote:
> I'm looking for a way to update data in one table with data from
> another table.
>
> More specifically, if there are any records in the two tables which
> have matching primary keys but differing data in at least one other
> column, I need to update the values in the old table from the new
> table.
>
> I can't just do a blanket update of every record, because that would
> result in a lot of unneccesary updates -- I only want to update
> records which actually have different data.
>
> I tried an UPDATE FROM statement, but Oracle doesn't support that.
>
> I tried a MERGE INTO statement, but Oracle 9i requires action to be
> taken both when the records match *and* when they don't. Bah!
>
> Any suggestions?

And a correlated update won't work? Why?
http://www.psoug.org
click on Morgan's Library
click on UPDATE
--
Daniel A. Morgan
University of Washington
damorgan@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Thanks! I'll give that a try.

Can a correlated update operate on more than one field at a time? And
if not, would updating each field in a record result in several
separate updates? I have a trigger that fires on updates, and it would
be really nice to have just one trigger activation per record, even if
that record has several fields to update.

Oops, now I see the multi-column update example. I'll give that a
shot.

Thanks again.