UPDATE (E-SQL)

The UPDATE statement changes data in the row where the cursor is currently positioned.

Syntax

UPDATE {table_name | view_name} SET {column=expression[,...]} WHERE CURRENT OF cursor_name

Arguments

table_name

Is the table to be updated.

view_name

Is the view to be updated.

column

Is the column to be updated.

expression

Is the value of a particular column name. This value can be an expression or a null value.

cursor_name

Is a previously declared, opened, and fetched cursor.

Remarks

In addition to having the searched update functionality of the Transact-SQL UPDATE statement, the Embedded SQL UPDATE statement includes functionality that is known as positioned update. Positioned update changes the row most recently fetched by a cursor.

Note In a positioned update, 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 update that uses a browse cursor, the SELECT statement used to open the cursor must include a FOR BROWSE clause, and the base table(s) must include a timestamp column. If an error prevents any row found by the search condition WHERE CURRENT OF 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), and the row has been changed after the last FETCH statement, no changes are made to the database. The value of SQLCODE is set to -532, which means that a positioned UPDATE or DELETE statement failed because of a conflict with another user. Also, the SQLERRD3 field in the SQLCA data structure shows no rows processed.

Examples

while (SQLCODE == 0)

{
    EXEC SQL FETCH c1 INTO :fname,:lname;

    if (SQLCODE == 0)

    {

        printf("%s %s", fname, lname);

        printf("Update? ");

        scanf("%c", &reply);

        if (reply == 'y')

        {

            printf("New last name? ");

            scanf("%s", &lname);

            EXEC SQL

                UPDATE authors SET au_lname=:lname

                WHERE CURRENT OF c1;

            printf("update sqlcode= %s", SQLCODE);

        }

    }
}

See Also