Re: I need to be able to use SQL to update a file from another file updating multiple records at a time.

<replying to MIDRANGE-L instead of the RPG-L>

The correct syntax is:

Update EMS40DATA/INWHS01P
set (IWADD1, IWADD2, IWADD3, IWALWS, IWADD4, IWDESC)
= (Select LADD1, LADD2, LADD3, LALWH, LCOUN, LDESC
from SrcLibrary/SrcFile
where INWHS01P.keyfieldA = SrcFile.KeyFieldB
)

assuming keyfield* is a unique field in both files, adjust to whatever
the unique keys are.

Note, the above assumes a 1-to-1 match between records. For every
value of keyfieldA in INWHS01P there's a record with the same value in
keyfieldB.

If that is not the case, then the select from SrcFile will return NULL
for all fields selected from SrcFile

If the fields in INWHS01P don't allow NULL you have two choices
(Note the use of correlation names A&B instead of the original file name):
1) Don't update records that don't have a match (this I what I believe
you were trying to do)

Update EMS40DATA/INWHS01P
set (IWADD1, IWADD2, IWADD3, IWALWS, IWADD4, IWDESC)
= (Select LADD1, LADD2, LADD3, LALWH, LCOUN, LDESC
from SrcLibrary/SrcFile
where INWHS01P.keyfieldA = SrcFile.KeyFieldB
)
where exists (select * from SrcLibrary/SrcFile C
where A.KeyFieldA = C.KeyFieldB
)


2) Update the INWHS01P fields with their original value

Update EMS40DATA/INWHS01P A
set (IWADD1, IWADD2, IWADD3, IWALWS, IWADD4, IWDESC)
= (Select coalesce(LADD1,A.IWADD1),
coalesce(LADD2,A.IWADD2),
coalesce(LADD3, A.IWADD3),
coalesce(LALWH, A.IWALWS),
coalesce(LCOUN, A.IWADD4),
coalesce(LDESC, A.IWDESC)
from SrcLibrary/SrcFile B
where A.keyfieldA = B.KeyFieldB
)

Although as I look at this I can't recall if I can use the A.xxx
fields in the coalesce you may have to do it this way
Update EMS40DATA/INWHS01P A
set (IWADD1, IWADD2, IWADD3, IWALWS, IWADD4, IWDESC)
= (Select coalesce(LADD1,C.IWADD1),
coalesce(LADD2,C.IWADD2),
coalesce(LADD3, C.IWADD3),
coalesce(LALWH, C.IWALWS),
coalesce(LCOUN, C.IWADD4),
coalesce(LDESC, C.IWDESC)
from EMS40DATA/INWHS01P C
left outer join SrcLibrary/SrcFile B
on C.keyfieldA = B.KeyFieldB
where A.keyfieldA = C.KeyFieldB
)

HTH,
Charles Wilt







On Wed, May 26, 2010 at 10:09 AM, Darryl Freinkel
<dfreinkel@xxxxxxxxxxxxxxxxx> wrote:

In doing research on using the SQL UPDATE command I found this structure
that works for other non IBM i systems.



Update EMS40DATA/INWHS01P set (IWADD1, IWADD2, IWADD3, IWALWS, IWADD4,
IWDESC)

= (Select LADD1, LADD2, LADD3, LALWH, LCOUN, LDESC  from SrcLibrary/SrcFile

Where exists (Select IWWHS from EMS40DATA/INWHS01P ))



IBM does not support this command in SQL and can only process 1 record at a
time. I have multiple records to process.