Re: help how to update column from other table

In article <2v74te$od_at_bengal.oxy.edu>, mcmanus_at_bengal.oxy.edu (Sally S. McManus) wrote:

>
> I need help with sql. I need the syntax for updating a field in one
> table with data in a field from another table.
>
> Thank you.
>
> Sally

what you probably need is called a "correlated update" and is documented in the section on the update statement in the sql reference manual.

it is very important to note that you usually need TWO where clauses, e.g.:

   update t1 a set x =

           (select y from t2
             where key = a.key)
    where exists
          ( select null from t2
             where key = a.key );

a variety of other styles will work, e.g.:

   update t1 a set x =

           (select y from t2
             where key = a.key)

changing the style may change performance.

the second where clause tells *which* rows to update. without it *all* rtows will be updated. if all rows are updated and some don't have matching keys, those without a match will be given null values.

the inner select must return exactly one row for each key.

the inner select is effectively executed once for each row updated, so it is important to have a good index on t2.

the above example is about as simple as it gets. these can be arbitrarily complex. Received on Tue Jul 05 1994 - 21:08:49 CEST