Using a Positioned Update

Go Up to Updating Multiple Rows

Use cursors to select rows for update when prompting users for changes on a row-by-row basis, and displaying pre- or post-modification values between row updates. Updating through a cursor is a seven-step process:

1. Declare host-language variables needed for the update operation.

2. Declare a cursor describing the rows to retrieve for update, and include the FOR UPDATE clause in DSQL. For more information about declaring and using cursors, see Selecting Multiple Rows.

3. Open the cursor.

4. Fetch a row.

5. Display current values and prompt for new values.

6. Update the currently selected row using the WHERE CURRENT OF clause.

7. Repeat steps 3 to 7 until all selected rows are updated.

For example, the following C code fragment updates the POPULATION column by user-specified amounts for cities in the CITIES table that are in a country also specified by the user:

. . .
EXEC SQL
BEGIN DECLARE SECTION;
char country[26], asciimult[10];
int multiplier;
EXEC SQL
END DECLARE SECTION;
. . .
main ()
{
EXEC SQL
DECLARE CHANGEPOP CURSOR FOR
SELECT CITY, POPULATION
FROM CITIES
WHERE COUNTRY = :country;

printf("Enter country with city populations needing adjustment: ");
gets(country);
EXEC SQL
OPEN CHANGEPOP;
EXEC SQL
FETCH CHANGEPOP INTO :country;
while(!SQLCODE)
{
printf("\nPercent change (100%% to -100%%:");
gets(asciimult);
multiplier = atoi(asciimult);
EXEC SQL
UPDATE CITIES
SET POPULATION = POPULATION * (1 + :multiplier / 100)
WHERE CURRENT OF CHANGEPOP;
EXEC SQL
FETCH CHANGEPOP INTO :country;
if (SQLCODE && (SQLCODE != 100))
{
isc_print_sqlerr(SQLCODE, isc_status);
EXEC SQL
ROLLBACK RELEASE;
exit(1);
}
}
EXEC SQL
COMMIT RELEASE;
}

Important: Using FOR UPDATE with a cursor causes rows to be fetched from the database one at a time. If FOR UPDATE is omitted, rows are fetched in batches.