Updating Multiple Rows

From InterBase
Jump to: navigation, search

Go Up to Updating Data (Embedded SQL Guide)


There are two basic methods for modifying rows:

  • The searched update method, where the same changes are applied to a number of rows, is most useful for automated updating of rows without a cursor.
  • The positioned update method, where rows are retrieved through a cursor and updated row by row, is most useful for enabling users to enter different changes for each row retrieved.

A searched update is easier to program than a positioned update, but also more limited in what it can accomplish.


Using a Searched Update

Use a searched update to make the same changes to a number of rows. The UPDATE SET clause specifies the actual changes that are to be made to columns for each row that matches the search condition specified in the WHERE clause. Values to set can be specified as constants or variables.

For example, the following C code fragment prompts for a country name and a percentage change in population, then updates all cities in that country with the new population:

. . .
EXEC SQL
BEGIN DECLARE SECTION;
char country[26], asciimult[10];
int multiplier;
EXEC SQL
END DECLARE SECTION;
. . .
main ()
{
printf("Enter country with city populations needing adjustment: ");
gets(country);
printf("\nPercent change (100%% to -100%%:");
gets(asciimult);
multiplier = atoi(asciimult);
EXEC SQL
UPDATE CITIES
SET POPULATION = POPULATION * (1 + :multiplier / 100)
WHERE COUNTRY = :country;

if (SQLCODE && (SQLCODE != 100))
{
isc_print_sqlerr(SQLCODE, isc_status);
EXEC SQL
ROLLBACK RELEASE;
}
else
{
EXEC SQL
COMMIT RELEASE;
}
}
Important: Searched updates cannot be performed on arrays of data types.


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:

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.

Advance To: