Deleting Multiple Rows

Go Up to Deleting Data (Embedded SQL Guide)

There are two methods for modifying rows:

• The searched delete method, where the same deletion condition applies to a number of rows, is most useful for automated removal of rows.
• The positioned delete method, where rows are retrieved through a cursor and deleted row by row, is most useful for enabling users to choose which rows that meet certain conditions should be removed.

A searched delete is easier to program than a positioned delete, but less flexible.

Using a Searched Delete

Use a searched delete to remove a number of rows that match a condition specified in the WHERE clause. For example, the following C code fragment prompts for a country name, then deletes all rows that have cities in that country:

. . .
EXEC SQL
BEGIN DECLARE SECTION;
char country[26];
EXEC SQL
END DECLARE SECTION;
. . .
main ()
{
printf("Enter country with cities to delete: ");
gets(country);
EXEC SQL
DELETE FROM CITIES
WHERE COUNTRY = :country;

if(SQLCODE && (SQLCODE != 100))
{
isc_print_sqlerr(SQLCODE, isc_status);
EXEC SQL
ROLLBACK RELEASE;
}
else
{
EXEC SQL
COMMIT RELEASE;
}
}

Using a Positioned Delete

Use cursors to select rows for deletion when users should decide deletion 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 delete operation.
2. Declare a cursor describing the rows to retrieve for possible deletion, and include the FOR UPDATE clause. 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 permission to delete.
6. Delete the currently selected row using the WHERE CURRENT OF clause to specify the name of the cursor.
7. Repeat steps 3 to 7 until all selected rows are deleted.

For example, the following C code deletes rows in the CITIES table that are in North America only if a user types Y when prompted:

. . .
EXEC SQL
BEGIN DECLARE SECTION;
char cityname[26];
EXEC SQL
END DECLARE SECTION;
char response[5];
. . .
main ()
{
EXEC SQL
DECLARE DELETECITY CURSOR FOR
SELECT CITY,
FROM CITIES
WHERE CONTINENT = 'North America';
EXEC SQL
OPEN DELETECITY;
while (!SQLCODE)
{
EXEC SQL
FETCH DELETECITY INTO :cityname;
if (SQLCODE)
{
if (SQLCODE == 100)
{
printf('Deletions complete.');
EXEC SQL
COMMIT;
EXEC SQL
CLOSE DELETECITY;
EXEC SQL
DISCONNECT ALL:
}
isc_print_sqlerr(SQLCODE, isc_status);
EXEC SQL
ROLLBACK;
EXEC SQL
DISCONNECT ALL;
exit(1);
}
printf("\nDelete %s (Y/N)?", cityname);
gets(response);
if(response[0] == 'Y' || response == 'y')
{
EXEC SQL
DELETE FROM CITIES
WHERE CURRENT OF DELETECITY;
if(SQLCODE && (SQLCODE != 100))
{
isc_print_sqlerr(SQLCODE, isc_status);
EXEC SQL
ROLLBACK;
EXEC SQL
DISCONNECT;
exit(1);
}
}
}

Advance To:

• Deleting Through a View