SQL/MX Programming Manual for C and COBOL (G06.24+, H06.03+)

Static Rowsets
HP NonStop SQL/MX Programming Manual for C and COBOL—523627-004
7-18
Updating Rows by Using Rowset Arrays
Use this general syntax:
set-clause
The expression in a set-clause can contain array host variables. When array
host variables are present in the search-condition, two alternatives exist for
the set-clause expression:
•
Scalar host variables only. In this case, all matching rows are updated with
identical values, obtained by evaluating the scalar expression. This case is
shown in the next example.
•
Some array host variables. You can use a rowset in the SET clause only if you
have a rowset in the WHERE clause. If the size of the rowsets are not the
same in the SET and WHERE clauses, the smaller of the two sizes are used
and all rowset elements beyond the smaller size are ignored. All rows returned
due to the first element in the search-condition array are updated using
the value obtained by evaluating the first element in the set-clause array. All
matching rows due to the second element in the search-condition array
are updated using the second element in the set-clause array, and so on.
search-condition
must contain host variable arrays if you use rowsets in an UPDATE statement. The
use of rowset arrays for input is similar to a looping mechanism whereby the same
statement is executed multiple times with a different set of values for input each
time.
For complete syntax, see the UPDATE statement in the SQL/MX Reference Manual.
Example
This example updates the SALARY column of all rows in the EMPLOYEE table where
the JOBCODE value is equal to one of the values in the hva_jobcode host variable
array. The UPDATE statement is executed for each matching job code:
EXEC SQL BEGIN DECLARE SECTION;
char SQLSTATE[6];
ROWSET[5] unsigned NUMERIC (4) hva_jobcode;
unsigned NUMERIC (4,2) hv_inc;
...
long numrows;
EXEC SQL END DECLARE SECTION;
...
/* Input the salary increment. */
...
/* Populate the rowset in some way. */
hva_jobcode[0] = 100;
hva_jobcode[1] = 200;
UPDATE table-name
SET set-clause [,set-clause]...
WHERE search-condition
C