%UpdatePairs meta-SQL element

Syntax

%UpdatePairs(recname [correlation_id])

Description

The %UpdatePairs construct produces a comma-separated list of fieldname = 'fieldvalue' phrases for each changed field of the given record. Input processing is applied to the values in the following ways:

  • If the field is a Date, a Time, or a DateTime value, its value is automatically wrapped in %Datein, %TimeIn, or %DateTimeIn, respectively.

  • If the field is a string, its value is automatically wrapped in quotes.

  • If the field has a null value, NULL is the given value.

Note:

This meta-SQL construct can only be used in PeopleCode programs, not in Application Engine SQL actions. Also, this meta-SQL construct is not implemented for COBOL.

Parameters

Parameter Description

recname

Specify the name of the record to use for updating. This can be a bind variable, a record object, or a record name in the form recname. You can't specify RECORD. recname, a record name in quotation marks, or a table name.

correlation_id

Identify the single-letter correlation ID to relate the record specified by recname and its fields.

Example

Suppose that the record &REC has one key: FNUM, and the FCHAR field has changed. Here is an example:

Local record &REC; 
&REC = CreateRecord(RECORD.MYRECORD); 
&REC.FNUM.Value = 27; 
&REC.FCHAR.Value = 'Y'; 
SQLExec("Update TABLE set %UpdatePairs(:1) where %KeyEqual(:1)", &REC)

The example expands to:

"Update TABLE set FCHAR = 'Y' where FNUM = 27"

The following example updates all the fields on a base record (&REC) that are not also fields on the related language record (&REC_RELATED_LANG). It creates a holding record (&REC_TEMP), copies the fields to update from the base record to the holding record, and then uses the holding record for the update.

&UPDATE = CreateSQL("Update %Table(:1) set %UpdatePairs(:1) Where %KeyEqual(:2)"); 
&REC_TEMP = CreateRecord(@("RECORD." | &REC.Name)); 
&FIELD_LIST_ARRAY = CreateArray(); 
For &I = 1 to &REC_RELATED_LANG.FieldCount 
   &FIELD_LIST_ARRAY.Push(&REC_RELATED_LANG.GetField(&I).Name); 
End-For; 
For &I = 1 to &REC.FieldCount 
   If &FIELD_LIST_ARRAY.Find(&REC.GetField(&I).Name) = 0 then 
      &REC_TEMP.GetField(&I).Value = &REC.GetField(&I).Value; 
   End-If; 
End-For; 
&UPDATE.Execute(&REC_TEMP, &REC);