This article contains 3 examples:
– MS Access;
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.