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

docs.intersystems.com

Caché SQL Reference

WHERE CURRENT OF

[Home] [Back]

InterSystems: The power behind what matters

Caché Development References > Caché SQL Reference > SQL Commands > WHERE CURRENT OF

Search:

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

Synopsis

WHERE CURRENT OF cursor

Arguments

cursor

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

Description

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:

   &sql(DELETE FROM Sample.Employees WHERE CURRENT OF EmployeeCursor)

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.

UPDATE Restriction

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>.

Examples

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)