Updates the current row in a result set cursor.

Syntax

---

UPDATE <CursorName>
SET <ColumnName> = <Value> [,<ColumnName> = <Value>])

Usage

---

Use this statement to update the current row in a result set cursor. The SET clause is used to specify which columns you want to update and the values to assign to the columns. Each value can be any valid SQL expression.

Examples

---

-- This procedure uses an IF statement
-- to conditionally test if the State column
-- is equal to 'FL', and if so, to change it
-- to 'NY'

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

CREATE PROCEDURE UpdateState()
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
            UPDATE CustCursor SET 'State'='NY';
         END IF;
         FETCH NEXT FROM CustCursor ('State') INTO State;
      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 UPDATE 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: