Using &NDB Verbs › Work with NDBs › Update Records in an NDB

Update Records in an NDB

A record in an NDB can be updated using the &NDBUPD verb. Unlike the &FILE PUT verb, an &NDBUPD verb only updates the nominated fields. All other fields retain their existing value. The &FILE PUT verb replaces the entire record.

The RID(s) of the record(s) to be updated must be known. Normally, the RID is determined by a preceding &NDBGET or &NDBSCAN.

To update just a few (fixed amount) fields, code:

&NDBUPD dbname RID=rid DATA name1 = value1 name2 = value2 

where name1, name2, ... are the names of the fields to be updated, and value1, value2, ... are the values.

If you need to update a large number of fields, or you are not sure of the exact content of the update, an alternative format of the &NDBUPD verb can be used:

&NDBUPD dbname RID=n START
&NDBUPD dbname DATA name1 = value1
&NDBUPD dbname DATA name2 = value2
&NDBUPD dbname DATA name3 = value3
&NDBUPD dbname DATA name4 = value4
&NDBUPD dbname END

This syntax allows any number of fields to be updated.

The fields need not be in any order, and the collection of &NDBUPD statements need not be adjacent. For example, a loop could be used, with complex substitution, to build the list of field names and values:

&NDBUPD MYNDB RID=&UPDRID START
&I = 1
&DOWHILE &I LE &NFLDS
   &NDBUPD MYNDB DATA &FN&I = &VALUE
   &I = &I + 1
&DOEND
&NDBUPD MYNDB END -* this statement calls the DBMS

In this example, we loop through a table of field names and values, updating each nominated field.

Note: Fields defined with UPDATE=NO can be specified in the update list, as long as the same value as is currently in the record is supplied.

A field that is to be set to null (not present) can be represented by coding:

fieldname = &NULL

where &NULL is an undefined variable. If the field definition has NULLFIELD=NO, an error response will be given.

Following the &NDBUPD or &NDBUPD END, if you are using START/DATA/END, the &NDBRC system variable contains 0 if no errors were encountered. If not 0, an error response indicates the problem, and &NDBERRI might have additional information. An error message is also displayed unless &NDBCTL MSG=NO is in effect.

If the response is 0, &NDBRID contains the RID that was supplied on the &NDBUPD verb.