A positioned DELETE statement uses the WHERE CURRENT OF clause to remove the row pointed to by the current cursor position. The cursor is one defined for a previously-created multi-row SELECT statement in your SAM.
In order to reference a cursor in the positioned DELETE statement, you must create a multi-row SELECT statement in your SAM before you create a positioned DELETE statement.
The positioned DELETE statement and the multi-row SELECT statement that it references should operate on a singe database table.
If you attempt to create a positioned DELETE statement without first creating a multi-row SELECT statement, DB/Assist displays an error message and does not allow you to create the positioned DELETE statement.
For example, the following positioned DELETE statement deletes the row in the Staff table pointed to by the current position of a cursor named SQL_CUR_1:
DELETE FROM STAFF
WHERE CURRENT OF SQL_CUR_1
To build the required multi-row SELECT statement and the positioned DELETE statement that references it, follow these steps:
1.Build the following multi-row SELECT statement named
SelectStaff that retrieves all rows from the Staff table:
SELECT STAFF.ID, STAFF.NAME, STAFF.DEPT, STAFF.JOB, STAFF.YEARS, STAFF.SALARY, STAFF.COMM
FROM DB2ADMIN.STAFF STAFF
1.To build the positioned DELETE statement, select SQL Statement Ø Tables or the Tables button.
The DELETE/FROM Clause dialog box appears, listing the tables in the connected database.
2.Select the STAFF table in the Table list box. (You can only select columns from a singe table when building a DELETE statement.)
The names of all columns in the STAFF table appear selected in the Columns list box, as shown below. (You cannot select individual columns when building a DELETE statement.)
DELETE/FROM Clause Dialog Box
3.Select the OK push button in the DELETE/FROM Clause dialog box.
DB/Assist adds the positioned DELETE statement built so far to the SQL Editor window and to the SAM containing this statement, as follows:
DELETE FROM STAFF
The WHERE CURRENT OF Clause dialog box appears so you can build the WHERE CURRENT OF syntax for this statement. The WHERE CURRENT OF Clause dialog box lists the statement and cursor names defined for each multi-row SELECT statement in the current SAM
1.In the WHERE CURRENT OF Clause dialog box, select the row containing the SelectStaff statement and cursor name (SQL_CUR_1) from the list box, as shown below.
WHERE CURRENT OF Clause Dialog Box
2.Select the OK push button in the WHERE CURRENT OF Clause dialog box.
The WHERE CURRENT OF Clause dialog box closes, and DB/Assist adds the WHERE CURRENT OF clause to the DELETE statement in the SQL Editor window, as follows:
DELETE FROM STAFF
WHERE CURRENT OF SQL_CUR_1
For a sample ESL program that shows how to use positioned UPDATE and positioned DELETE statements, see Sample Program for Positioned SQL.
