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

["Stone, Joel" <Joel.Stone@xxxxxxxxxx>]

Thanks Glenn.  I saw that article when I googled the issue!

The problem is that example doesn't retrieve any info from fileB.



-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Glenn Gundermann
Sent: Wednesday, March 30, 2016 5:04 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: Re: SQL v5r4: any simple example of how to update one field1 in fileA from a different field2 in fileB

I keep a collection of sample code.  Sorry if I don't have time to change
the names to meet your example.

Here is 1 way:

update salesordh as h

   set shipdate = shipdate + 7 days

 where exists

   (select *

      from customers as c

     where type = 'B'

       and c.companyno  = h.companyno

       and c.customerno = h.customerno)


Here is a better way:


update salesordh

   set shipdate = shipdate + 7 days

 where (companyno, customerno) in

          (select companyno, customerno

             from customers

            where type = 'B')


I saw these examples in this article:

http://www.itjungle.com/fhg/fhg041410-story02.html



Yours truly,

Glenn Gundermann
Email: glenn.gundermann@xxxxxxxxx
Work: (416) 675-9200 ext. 89224
Cell: (416) 317-3144


On 30 March 2016 at 17:57, 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?
>
> (I cant use MERGE as it must function on v5r4 for a few more months)
>
> Thanks!