Positioned DELETE statement: Positioned DELETE statement is used to delete a row which is currently pointed by the cursor in embedded SQL. After the row has been deleted the cursor is moved to another location by using the FETCH statement.
DELETE FROM table_name WHERE CURRENT OF cursor_name
EXEC SQL DELETE FROM employees WHERE CURRENT OF emplcur;
Positioned UPDATE statement: Positioned UPDATE statement is used to update a row which is currently pointed by the cursor in embedded SQL. After the row has been updated, it still is the current row of the cursor.
UPDATE table_name SET column_name=expression WHERE CURRENT OF cursor_name
EXEC SQL UPDATE employees SET emplname=:empl_name WHERE CURRENT OF emplcur;
Rules for using positioned DELETE and positioned UPDATE statements:
1) User must have the update and delete privileges for the table which is being updated.
2) There should only be one table in the Positioned DELETE and Positioned UPDATE statement.
3) ORDER BY clause, DISTINCT keyword, GROUP BY clause, and HAVING clause cannot be used with the positioned DELETE and positioned UPDATE statements.