Positioning the Cursor for the UPDATE

How does the program know which row in the table to update?

The FOR UPDATE Clause topic shows that when the cursor was declared, it contained a WHERE condition to indicate which rows were affected by the SQL query. The WHERE CURRENT OF clause uses this information to position the cursor. This is called a positioned update.

Syntax

EXEC SQL
    UPDATE tablename SET field = 'new_value'
    WHERE CURRENT OF cursorname
END-EXEC

where cursorname is the name of the cursor containing the SQL query.

Example

EXEC SQL
    UPDATE CUSTOMER SET C_INFO = 'Revised' 
    WHERE CURRENT OF COBCUR1
END-EXEC

The FOR UPDATE Clause topic shows a cursor declared to select the C_FIRST_NAME and C_LAST_NAME fields from the "customer" table in those instances where C_LAST_NAME was equal to Snead.

When the cursor gets to a row that meets this condition, the value of the C_INFO field will be updated to Revised.

Note: You can also use cursors to perform positioned deletes in AcuSQL. See any commercially available SQL text for information on positioned deletes.