This article contains 3 examples:

– T-SQL;
– MS Access;
– AS/400.

This example shows a standard T-SQL syntax tested on MS SQL 2005 server:

update P
set P.CertMemberLevelID = Z.CertLevelTo
from   UPM_MemberPermissionMask P
INNER JOIN zzzCertRegistryUpdate Z
ON P.MemberID = Z. Cert2ID

Here we are updating one filed CertMemberLevelID in table UPM_MemberPermissionMask based on field Cert2ID in table zzzCertRegistryUpdate.

This next example was generated using MS Access 2007 syntax:

UPDATE CRPDTA_F1201
 INNER JOIN CatCode7 ON
 CRPDTA_F1201.FANUMB = CatCode7.XXNUMB
 SET CRPDTA_F1201.FAFA7 = [CatCode7].[XXFA7]

A field FAFA7 in CRPDTA_F1201 is being updated.
Table  CatCode7 contains 2 fields:
– ID Link field XXNUMB and
– Update values field XXFA7

SQL statement JOINs two files on a link field
CRPDTA_F1201.FANUMB = CatCode7.XXNUMB
and updates the field CRPDTA_F1201.FAFA7 based on value of field [CatCode7].[XXFA7]

Next two examples were run on IBM AS/400 (iSeries).  Note that syntax is completely different:

UPDATE PRODLIB/F1201 SET FAFA5 = (SELECT FHADDS FROM
TEMP/F1206 WHERE FANUMB = FHNUMB) WHERE  FANUMB IN
 (SELECT FHNUMB FROM TEMP/F1206)

UPDATE F0116 a SET a.ALADDZ = ( select b.ZZADDZ from          
TEMP/F0116_ZIP b where a.alan8 = b.alan8) WHERE a.alan8 in 
(select b.alan8 from TEMP/F0116_ZIP b)

In this example file F1206 is just an empty shell, and only 2 fields are used: FHNUMB as a connecting 2 table reference filed,  and FHADDS as a filed containing new updated value.