Deleting Multiple Rows
From InterBase
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:
- Declare host-language variables needed for the delete operation.
- 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. - Open the cursor.
- Fetch a row.
- Display current values and prompt for permission to delete.
- Delete the currently selected row using the
WHERE CURRENT OF
clause to specify the name of the cursor. - 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); } } }