SQL Programming Manual for TAL

NonStop SQL Statements and Directives
HP NonStop SQL Programming Manual for TAL—527887-001
3-43
UPDATE
•
A set of rows, one row at a time using a cursor (a mechanism for dealing with a set
of rows returned in sequence to a program)
A lock on an updated row is held until the TMF transaction is committed or rolled back
(audited table) or until the program releases the lock (nonaudited table).
To execute an UPDATE statement, a program's process accessor ID (PAID) must have
read and write access to the table or view being updated. A program's PAID must also
have read access to any table or view specified in subqueries of the search condition.
In general, specifying an update operation programmatically in a TAL program is the
same as specifying an update operation using SQLCI commands. With SQLCI, you
specify the new values in an UPDATE statement, while in a program, you set one or
more host variables to the new values and then use the host variables in the UPDATE
statement.
The UPDATE statement updates rows in sequence. If an error occurs, NonStop SQL
returns an error code to the SQLCODE variable and terminates the operation. This
table shows the SQLCODE values that NonStop SQL returns after an UPDATE
statement.
The SQLCA structure contains the number of rows updated. To obtain the contents of
the SQLCA, use the SQLCADISPLAY or SQLCATOBUFFER procedure.
Updating a Single Row
A single-row update operation updates a single row in a table. This example updates a
single row of the ORDERS table that contains information about order number 200038.
This example declares the host variables :ORDERS and :NEWDATE, which serve as
input variables in the UPDATE statement. (In a program, a user enters values for
:ORDERS and :NEWDATE; in the example, they are set using assignment
statements.)
! Variable declarations:
EXEC SQL BEGIN DECLARE SECTION;
STRUCT .orders;
BEGIN
INT(32) ordernum;
STRING orderdate[0:9];
STRING deliv^date[0:9];
INT salesrep;
INT custnum;
END;
string newdate[0:9];
SQLCODE Value Description
0 The UPDATE statement was successful.
100 No rows were found for a search condition.
< 0 An error occurred; SQLCODE contains the error number.
> 0 (not 100) A warning occurred; SQLCODE contains the first warning number.