DELETE (POSITIONED) (E-SQL)

The DELETE (POSiTIONED) statement removes the row where the cursor is currently positioned.

Syntax

DELETE [FROM] {table_name | view_name} WHERE CURRENT OF cursor_name

Arguments

FROM

Is an optional keyword included for compatibility with other versions of ESQL/C.

table_name

Is the same table used in the SELECT statement portion of the DECLARE CURSOR STATEMENT.

view_name

Is the same view used in the SELECT statement portion of the DECLARE CURSOR statement.

cursor_name

Is a previously declared, opened, and fetched cursor. Cursor names can have as many as 30 characters, and can include alphanumeric characters and any symbols that are legal in file names. Hyphens (-) are not permitted. The first character must be a letter.

Remarks

In addition to having the functionality of the Transact-SQL DELETE statement, the Embedded SQL DELETE statement includes functionality known as positioned delete, which deletes the row most recently fetched by a cursor. The DELETE statement used in standard Transact-SQL statements is known as a searched delete.

Note that a positioned delete has no search condition. The WHERE CURRENT OF option is used in place of a search condition clause. The WHERE CURRENT OF option cannot be used in a PREPARE statement.

In a positioned delete that uses a browse cursor, the SELECT statement used to open the cursor must include a FOR BROWSE clause. The base table(s) must include a timestamp column. If an error prevents any row found by the search condition from being deleted, no changes are made to the database.

When using a browse cursor, or a standard cursor with optimistic concurrency control (SET CONCURRENCY with the OPTCC or OPTCCVAL option), if the row has been changed after the last FETCH statement, no changes are made to the database and the value of SQLCODE is set to -532. Also, the SQLERRD3 field in the SQLCA data structure shows that no rows were processed.

Examples

EXEC SQL DECLARE c1 CURSOR FOR

    SELECT au_fname, au_lname FROM authors FOR BROWSE;

EXEC SQL OPEN c1;

while (SQLCODE == 0)

{

    EXEC SQL FETCH c1 INTO :fname, :lname;

    if (SQLCODE == 0)

    {
        printf("%12s %12s\n", fname, lname);

        printf("Delete? ");

        scanf("%c", &reply);

        if (reply == 'y')

        {

            EXEC SQL DELETE FROM authors WHERE CURRENT OF c1;

            printf("delete sqlcode= %d\n", SQLCODE(ca));

        }

    }

}

See Also