Icon DELETE

Deletes the current row in a result set cursor.

Syntax
DELETE FROM <CursorName>

Usage
Use this statement to delete the current row in a result set cursor.

Examples
-- This procedure uses an IF statement
-- to conditionally test if the State column
-- is equal to 'FL', and if so, to delete the row

-- The whole update process is wrapped inside
-- of a transaction start..commit/rollback block

CREATE PROCEDURE DeleteFLCustomers()
BEGIN
   DECLARE CustCursor CURSOR WITH RETURN FOR Stmt;
   DECLARE State CHAR(2) DEFAULT '';

   PREPARE Stmt FROM 'SELECT * FROM Customer';

   OPEN CustCursor;

   START TRANSACTION ON TABLES 'Customer';
   BEGIN

      FETCH FIRST FROM CustCursor ('State') INTO State;

      WHILE NOT EOF(CustCursor) DO
         IF (State='FL') THEN
            DELETE FROM CustCursor;
            FETCH FROM CustCursor ('State') INTO State;
         ELSE
            FETCH NEXT FROM CustCursor ('State') INTO State;
         END IF;
      END WHILE;

      COMMIT;

   EXCEPTION
      ROLLBACK;
   END;
END

Required Privileges
If the result set cursor is a sensitive cursor, then the current user must be granted the DELETE and SELECT privileges on the table in the SELECT statement used to output the result set that the cursor is using. Please see the User Security topic for more information.

SQL 2003 Standard Deviations
This statement deviates from the SQL 2003 standard in the following ways:

DeviationDetails
ExtensionThis SQL statement is an ElevateDB extension.
Image