|
|
|
Host language variables can only be used within expressions in the set clause and the search_condition. (Variables used in search_conditions must denote constant values and cannot represent names of database columns or include any operators.) A host string variable can also replace the complete search condition, as when it is used with the Ingres forms system query mode.
The non-cursor update can be formulated as a repeated query by using the keyword repeated. Doing so reduces the overhead required to run the same update repeatedly within your program. The repeated keyword directs the DBMS Server to save the query execution plan when the update is first executed.
This encoding can account for significant performance improvements on subsequent executions of the same update. The repeated keyword is available only for non-cursor updates; it is ignored if used with the cursor version. Repeated update cannot be specified as a dynamic SQL statement.
If your statement includes a dynamically constructed search_condition, that is, if the complete search_condition is specified by a host string variable, do not use the repeated option to change the search_condition after the initial execution of the statement. The saved execution plan is based on the initial value of the search_condition and any changes to search_condition are ignored. This rule does not apply to simple variables used in search_conditions.
|
|
|
You must own the table or have update privilege. If the statement contains a where clause that specifies columns of the table being updated, you must have both select and update permissions; otherwise, update permission alone is sufficient.
|
|
|
The cursor version of update is similar to the interactive update, except for the where clause. The where clause, required in the cursor update, specifies that the update occur to the row the cursor currently points to. If the cursor is not pointing to a row, as is the case immediately after an open or delete statement, a runtime error message is generated indicating that a fetch must first be performed. If the row the cursor is pointing to has been deleted from the underlying database table (as the result, for example, of a non-cursor delete), no row is updated and the sqlcode is set to 100. Following a cursor update, the cursor continues to point to the same row.
Two cursor updates not separated by a fetch causes the same row to be updated twice if the cursor was opened in the direct update mode. If the cursor was opened in deferred update mode, more than one update cannot be issued against a row, and the update cannot be followed by a delete statement on the same row. Attempting to do either results in an error indicating an ambiguous update operation.
If the table was created with no duplicate rows allowed, the DBMS Server returns an error if attempt is made to insert a duplicate row.
In performing a cursor update, make sure that certain conditions are met:
When executing a cursor update dynamically, using the prepare statement, the cursor must be open before the cursor update statement can be prepared. The prepared statement remains valid while the cursor is open. If the named cursor is closed and reopened, re-prepare the corresponding update statement. If an attempt is made to execute the update statement associated with the previously open cursor, the DBMS Server issues an error.
Both the commit and rollback statements implicitly close all open cursors. A common programming error is to update the current row of a cursor, commit the change, and continue in a loop to repeat the process. This process fails because the first commit closes the cursor.
If the statement does not update any rows, the sqlcode of the SQLCA is set to 100. The sqlerrd(3) of the SQLCA indicates the number of rows updated by the statement.
|
|
|
The Update statement acquires page locks for each row in the table that is evaluated against the where clause.
|
|
|