Monday, April 6, 2009

AS400 SQL - Update a field from another file


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)

1 comment: