WHERE CURRENT OF - Caché SQL Reference - Caché & Ensemble 2018.1

An UPDATE/DELETE clause that specifies the current row using a cursor.

WHERE CURRENT OF cursor

cursor

Specifies that the operation is done at the current position of cursor, which is a cursor that points to the table.

The WHERE CURRENT OF clause can be used in a cursor-based Embedded SQL UPDATE or DELETE statement to specify the cursor positioned on the record to be updated or deleted. For example:

which deletes the row that the last FETCH command obtained from the "EmployeeCursor" cursor.

An Embedded SQL UPDATE or DELETE can use a WHERE clause (with no cursor), or a WHERE CURRENT OF with a declared cursor, but not both. If you specify an UPDATE or DELETE with neither WHERE nor WHERE CURRENT OF, all of the records in the table are updated or deleted.

When using a WHERE CURRENT OF clause, you cannot update a field using the current field value to generate an updated value. For example, SET Salary=Salary+100 or SET Name=UPPER(Name). Attempting to do so results in an SQLCODE -69 error: SET <field> = <value expression> not allowed with WHERE CURRENT OF <cursor>.

The following Embedded SQL example shows an UPDATE operation using WHERE CURRENT OF:

  NEW %ROWCOUNT,%ROWID
  &sql(DECLARE WPCursor CURSOR FOR 
        SELECT Lang FROM SQLUser.WordPairs
        WHERE Lang='Sp')
   &sql(OPEN WPCursor)
        QUIT:(SQLCODE'=0)
   FOR { &sql(FETCH WPCursor)
        QUIT:SQLCODE 
        &sql(UPDATE SQLUser.WordPairs SET Lang='Es'
       WHERE CURRENT OF WPCursor)
    IF SQLCODE=0 {
    WRITE !,"Update succeeded"
    WRITE !,"Row count=",%ROWCOUNT," RowID=",%ROWID }
    ELSE {
    WRITE !,"Update failed, SQLCODE=",SQLCODE }
    }
    &sql(CLOSE WPCursor)

The following Embedded SQL example shows a DELETE operation using WHERE CURRENT OF:

  NEW %ROWCOUNT,%ROWID
  &sql(DECLARE WPCursor CURSOR FOR 
        SELECT Lang FROM SQLUser.WordPairs
        WHERE Lang='En')
   &sql(OPEN WPCursor)
        QUIT:(SQLCODE'=0)
   FOR { &sql(FETCH WPCursor)
        QUIT:SQLCODE 
        &sql(DELETE FROM SQLUser.WordPairs
       WHERE CURRENT OF WPCursor)
    IF SQLCODE=0 {
    WRITE !,"Delete succeeded"
    WRITE !,"Row count=",%ROWCOUNT," RowID=",%ROWID }
    ELSE {
    WRITE !,"Delete failed, SQLCODE=",SQLCODE }
    }
    &sql(CLOSE WPCursor)