Using a Searched Update

Go Up to Updating Multiple Rows

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.