| Updating a field from another file on the AS/400 is done differently than
most SQL-based systems. This example uses a JD Edwards table. Here's how to do it: Syntax:
UPDATE Library/File1 Set Field1= (SELECT Field2 FROM Library/File2 where File1.Id1 =
File2.Id2) WHERE File1.Id1 IN (Select Id2 FROM Library/File2)
IMPORTANT!!!: If you leave off the second WHERE statement, you'll wipe
out the values of Field1 where the records don't match. Be sure to test this in
non-production or you might eliminate LOTS!!! of values in JDE F42119, possibly affecting
your performance review if you ever get one.
Example: Update JDE Open Orders Business unit with Address Book
Business Unit
UPDATE JDEDTA/F4211 SET SDMCU=(SELECT ABMCU FROM JDEDTA/F0101 WHERE
F4211.SDAN8=F0101.ABAN8) WHERE SDAN8 IN (SELECT ABAN8 FROM JDEDTA/F0101)
Note: If you use ODBC in Visual Basic or another program, change the
"/" to a dot. Example, UPDATE JDEDTA.F4211... Also, if field names are
different, do not use qualifiers in the where clauses.
So the above would look like this in ODBC:
UPDATE JDEDTA.F4211 SET SDMCU=(SELECT ABMCU FROM JDEDTA.F0101 WHERE SDAN8=ABAN8) WHERE
SDAN8 IN (SELECT ABAN8 FROM JDEDTA.F0101)
Mini Solve Home |
