Re: any simple example of how to update one field1 in fileA from a different field2 in fileB

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Works the same...

update fileA a
  set a.field1 =
                    (select b.field2 from fileB b
                      where a.keyfield1 = b.keyfield1 and a.key2 = b.key2
  where a.keyfield1 = (select keyfield1 from fileB b   where a.keyfield1 =
b.keyfield1 and a.key2= b.key2)

Or are you saying there is no key2 in a?

In that case is keyfield1 unique in both A and B?  If so, then you can use
your original statement...

If keyfield1 is unique in A but not unique in B, then of all the records in
B with a given keyfield1, how do you pick the one you want to use?

Charles

On Wed, Mar 30, 2016 at 5:42 PM, Stone, Joel <Joel.Stone@xxxxxxxxxx> wrote:

> When fileB has more than one key?
>
>
> The following works when fileB has only one key.  How can I do the same
> when fileB has two+ keys?
>
>
> update fileA a
>   set a.field1 =
>                     (select b.field2 from fileB b
>                       where a.keyfield1 = b.keyfield1
>   where a.keyfield1 = (select keyfield1 from fileB b   where a.keyfield1 =
> b.keyfield1)
>
>
>
> Is there a simpler method - maybe using JOIN ON?
>
> Thanks!
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.