Updating Multiple Rows
From InterBase
Using a Positioned Update
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:
- Declare host-language variables needed for the update operation.
- 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. - Open the cursor.
- Fetch a row.
- Display current values and prompt for new values.
- Update the currently selected row using the
WHERE CURRENT OF
clause. - 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
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.