× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



<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.

As an Amazon Associate we earn from qualifying purchases.

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.