Update Field Based on a Field in Another Table

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.

(Visited 7 times, 1 visits today)

1 Comment

  1. In MongoDB, is it possible to update the value of a field using the value from another field?

Your question, correction or clarification Ваш вопрос, поправка или уточнение

Editor on duty will review your comment. Can't wait? Discuss your topic right now in forums, if you register and login.     Required fields are marked with * Ваше послание пойдет на просмотр в редакцию. Не можете ждать? Обсудите ваш вопрос прямо сейчас в форуме Русский Круг , ecли вы зарегистрируетесь и войдёте под своим именем.     Поля, обязательные для заполнения, помечены *
* *