UPDATE is an executable command that can be issued interactively or embedded in a host language. UPDATE can be dynamically prepared.
Authority requirements for the UPDATE command depend on whether a table or view is being modified. If modifying a table, users need either UPDATE privileges on (or ownership of) the table, DBADM authority on the database containing the table, or overall SYSADM or SYSCTRL (some catalog tables only) authority for the location. If updating a view, users need UPDATE privileges on the view or SYSADM authority for the location.
The SELECT privilege is required on a searched update if the option for the SQL standard is set as follows:
If an expression that refers to a function is specified, the privilege set must include any authority that is necessary to execute the function.
UPDATE {table-name | view-name}[correlation-name]
SET assignment-clause
[WHERE search-condition]
[WITH {CS | RR | RS}]
[QUERYNO integer]
assignment-clause:
{column-name = {expression | NULL | scalar-subselect}
| (column-name[,...] = {{expression | NULL} [,...] | row-select}}[,...]
| table-name or view-name | Identifies the table or view to be updated. If a view, the view must be updatable. The name must not identify:
|
| correlation-name | Can be used within search-condition or a positioned UPDATE to designate the table or view. |
When updating tables with subselects, the subselect cannot reference a location other than the one referenced elsewhere in the command. That is, a referenced table and all subselects in a single SQL statement must be from the same location.
Invalid:
UPDATE tutorial.tutorial.practice SET pay = pay + 5 WHERE e_no IN SELECT e_no FROM debloc.debbie.employee WHERE st = "MD";
Valid:
UPDATE tutorial.tutorial.practice SET pay = pay + 5 WHERE e_no IN SELECT e_no FROM tutorial.debbie.employee WHERE st = "MD";
The following are examples of Searched UPDATE commands. The first example changes the value of the COLOR column for part number P2 to BROWN, and then adds 5 to its WEIGHT column value.
UPDATE part
SET color = "BROWN", weight = weight + 5
WHERE pno = "P2"
The next command doubles the STATUS column value of all London suppliers:
UPDATE supplier
SET status = status * 2
WHERE city = "LONDON"
The WHERE clause may also include a nested query, as in the following example, which updates the PARTSUPP table records containing the part numbers of any part made in Paris:
UPDATE partsupp
SET qty = qty-100
WHERE pno IN
SELECT pno
FROM part
WHERE city = "PARIS"
While an update command can only update one table, its WHERE clause may consist of a nested query that is a join of several tables. For example:
UPDATE supplier
SET status = 30
WHERE sno IN
SELECT sno
FROM partsupp, part
WHERE partsupp.pno = part.pno
AND qty > 50
Description
The Searched UPDATE, command locates all records that satisfy the conditions specified in the WHERE clause (providing all index, integrity and check conditions are met and reverified). If the WHERE clause is omitted in a Searched UPDATE all records in the table will be updated. The WHERE search condition may be an expression or a nested query. Note that the UPDATE command cannot include an ORDER BY clause.
When performing a Positioned UPDATE, the WHERE CURRENT OF clause must specify the name of an open cursor. Only the current record (as defined in the opened cursor) will be updated, with the SET clause specifying the new values for the indicated columns.
Introduces a list of one or more column names and the values to be assigned to the columns.
For a positioned update, allowable column names can be further restricted to those in a certain list. This list appears in the FOR UPDATE OF clause of the SELECT statement for the associated cursor. If the SELECT statement is dynamically prepared, the FOR UPDATE OF clause must always be present.
A view column derived from the same column as another column of the view can be updated, but both columns cannot be updated in the same UPDATE statement.
A column-name in an expression must identify a column of the table or view. For each row that is updated, the value of the column in the expression is the value of the column in the row before the row is updated.
UPDATE TABLE T1 SET COL1 = (SELECT COUNT(*) FROM TABLE2 T2 WHERE T2.COL5 = T2.COL3) WHERE COL3 = 'ABC'The subselect must not contain a GROUP BY or HAVING clause, and the subselect (or subquery within the subselect) cannot use the table or view being updated as its target. The subselect, however, can refer to columns of the table or view to be updated. The value of such a column in the subselect is the value of the column in the row before the row is updated. Correlated references to these columns are allowed only in a searched UPDATE and only in the search condition of the subselect.
Specifies an isolation level with which the statement is executed during a Searched UPDATE. You can override the isolation level of the statement using the WITH {RR | CS} clause. Because WITH UR only applies to read only operations, you cannot use it in an UPDATE statement. The WITH clause overrides the isolation level only for the statement in which it appears.
Specifies the number to be used for this SQL statement in EXPLAIN output and trace records. The number is used for the QUERYNO columns of the plan tables for the rows that contain information about this SQL statement.
If the clause is omitted, the number associated with the SQL statement is the statement number assigned during precompilation. Thus, if the application program is changed and then precompiled, that statement number might change.
Using the QUERYNO clause to assign unique numbers to the SQL statements in a program is helpful for simplifying the use of optimization hints for access path selection, if hints are used.
Unless appropriate locks already exist, one or more exclusive locks are acquired by the execution of a successful UPDATE statement. Until a commit or rollback operation releases the locks, only the application process that performed the insert can access the updated row. If LOBs are not updated, application processes that are running with uncommitted read can also access the updated row. The locks can also prevent other application processes from performing operations on the table. However, application processes that are running with uncommitted read can access locked pages and rows.
When a view is updated, the changes are reflected in the underlying database table. For a view to be updatable, the query used to define the view must satisfy the following criteria: