UPDATE

 
idmscu19
 
The UPDATE statement is a data manipulation statement that modifies the values in one or more rows of a table.
This article describes the following information:
2
2
Authorization
To issue an UPDATE statement, you must:
  • Hold the UPDATE privilege on or own the table, view, or table procedure named as the target of the update operation
  • Hold the SELECT privilege on or own each table, view, function, procedure and table procedure explicitly named in a subquery in the search condition in the WHERE parameter
Additional authorization requirements apply to:
  • A view named in
    table-reference
    ; each view named in the FROM parameter of such a view; each view named in the FROM parameters of those views, and so forth.
    For any such view, the owner of the view must hold the grantable UPDATE privilege on or own the table, view, or table procedure named in the FROM parameter of the view definition.
  • Each view named in the FROM parameter of a subquery in the search condition; each view named in the FROM parameter of such a view; each view named in the FROM parameters of those views, and so forth.
    For any such view, the owner of the view must hold the grantable SELECT privilege on or own each table, view, function, or table procedure named in the FROM parameter of the view definition.
Syntax
  ►►─── UPDATE 
table-reference
 ──┬───────────┬──────────────────────────────────►                                └─ 
alias
 ───┘            ┌─────────────────── , ────────────────────────┐  ►─── SET ─▼── 
column-name
 ── = ─┬─ 
value-expression
 ───┬─┴───────────────────►                                  ├─ NULL ───────────────┤                                  └─ (
query-expression
) ─┘  ►─┬─────────────────────────────────────────────────────┬────────────────────►◄    └─ WHERE ─┬─ 
search-condition
 ────────────────────────┤              └─ CURRENT OF ─┬─ 
cursor-name
 ──────────────┤                             └─ 
dynamic-name-clause
 ──────┘  
Expansion of dynamic-name-clause
  ►►─┬─────────────┬─ 
cursor-name
 ──────────────────────────────────────────────►◄    ├─ LOCAL ◄ ─┬─┘    └─ GLOBAL ──┘  
Parameters
  • table-reference
    Specifies the table, view, or table procedure whose rows are to be updated. Table-reference must not specify a procedure. If
    table-reference
    identifies a view:
    • The view must be updateable
    • The applicable rows are updated in the table from which the view is derived
    For expanded
    table-reference
    syntax, see Expansion of Table-reference.
    • alias
      Defines a new name to be used to identify the table, view or table procedure within the UPDATE statement.
      Alias
      must be a 1- through 18-character name that follows the conventions for SQL identifiers.
  • SET
    Specifies the columns to be updated and the value to be stored in each column.
  • column-name
    =
    Identifies a column to be updated.
    Column-name
    must identify a column in the table, view, or table procedure named in the UPDATE statement.
    Column-name
    must be unique within the SET parameter.
    In an UPDATE statement that includes the WHERE CURRENT OF
    cursor-name
    parameter,
    column-name
    must identify a column specified in the FOR UPDATE parameter of the DECLARE CURSOR statement that defines the named cursor.
  • value-expression
    Specifies the value to be stored in the named column. The data type of the value represented by
    value-expression
    must be compatible with the data type of the named column. For expanded
    value-expression
    syntax, see Expansion of Value-expression.
  • NULL
    Directs CA IDMS to store a null value in the named column. The column must be defined to allow null values.
  • query-expression
    Represents a value to be used for a column in an UPDATE column statement. The
    query-expression
    must return at most, one row and the result table of the
    query-expression
    must consist of a single column. 
    For more information about expanded
    query-expression
    syntax, see Expansion of Query-expression
  • WHERE
    Restricts the rows to be updated. If the UPDATE statement does not include the WHERE parameter, CA IDMS updates
    all
    rows in the specified table or view.
  • search-condition
    Specifies criteria a row must meet to be updated:
    • When the value of
      search-condition
      is true, the row is updated
    • When the value of
      search-condition
      is false, the row is not updated
    For expanded
    search-condition
    syntax, see Expansion of Search-condition.
  • CURRENT OF
    Specifies that only the row that corresponds to the current row of the named cursor is to be updated.
  • cursor-name
    Identifies the cursor whose current row will be updated.
    Cursor-name
    must identify an open cursor previously defined by a DECLARE CURSOR statement within the application program or by an ALLOCATE CURSOR statement executed within the same SQL transaction.
    This option may only be used in an UPDATE statement embedded in an application program.
  • dynamic-name-clause
    Identifies the cursor whose current row will be updated.
    This option may only be used in an UPDATE statement dynamically compiled using a PREPARE or EXECUTE IMMEDIATE statement.
Parameters for Expansion of dynamic-name-clause
  • LOCAL
    Indicates that the named cursor has a local scope and was defined using a DECLARE CURSOR statement or an ALLOCATE CURSOR statement. The default is LOCAL.
  • GLOBAL
    Indicates that the named cursor was created by an ALLOCATE CURSOR statement and is global in scope.
  • cursor-name
    Specifies the name of the cursor as an identifier.
    Cursor-name
    must identify an open cursor previously defined by a DECLARE CURSOR statement within the application program or by an ALLOCATE CURSOR statement executed within the same SQL transaction.
Usage
Searched Updates
An UPDATE statement that includes the WHERE
search-condition
parameter or does not include the WHERE parameter at all is called a searched update. Searched updates may be entered through the Command Facility, executed dynamically, and embedded within application programs.
Positioned Updates
An UPDATE statement that includes the WHERE CURRENT OF
cursor-name
parameter is called a positioned update. Positioned updates are valid only from within an application program.
Dynamic Positioned Updates
A dynamic positioned UPDATE statement is one that references a dynamic cursor. Such an UPDATE statement may be embedded within an application program or created dynamically using a PREPARE or EXECUTE IMMEDIATE statement.
A positioned UPDATE statement embedded in an application program may reference a static cursor or a dynamic cursor. A positioned UPDATE statement created dynamically using a PREPARE or EXECUTE IMMEDIATE statement can only reference a dynamic cursor.
Ambiguous Cursor References
When a dynamic positioned UPDATE statement is being created by a PREPARE or EXECUTE IMMEDIATE statement, it is possible that CA IDMS may not be able to determine which cursor is being referenced. This will occur if the application program contains a DECLARE CURSOR statement that defines a cursor having the referenced name and the program has also executed an ALLOCATE cursor statement that creates a cursor with the same name and a local scope. Under these conditions, CA IDMS cannot determine which of the two cursors is being referenced. To avoid such problems, it is advisable to use different names for cursors that are declared from those that are allocated with a local scope.
Restrictions on Table-reference
In a searched update, the table, view, or table procedure named in the UPDATE statement cannot also be named in the FROM parameter of any subquery included in the specified search condition; or, in the case of a view, in any search condition used in the view definition. The same restriction applies for any update that uses a subquery as the value to be stored in an updated column. Therefore, you cannot update data in a table from which you select in a subquery.
In a positioned update, the table, view, or table procedure named in the UPDATE statement must also be named in the FROM parameter of the query specification used in the definition of the named cursor.
Restriction on Value-expression
The value expression that specifies the value to be stored in a column cannot include any aggregate functions.
Cursor Position after a Positioned Update
After a positioned update, the position of the cursor named in the UPDATE statement remains unchanged.
Restrictions for Tables in Referential Constraints
If the table named in an UPDATE statement is the referencing table in a referential constraint, CA IDMS will update a row in the table only if, after the update operation, the foreign-key columns in the row satisfy either of the following conditions:
  • The columns must be all or partially null
  • The foreign-key values must match the referenced-column values in a row of the referenced table
If the table named in an UPDATE statement is the referenced table in a referential constraint, and the referencing table includes one or more rows whose foreign-key values match the referenced-column values of the row in the referenced table to be updated, CA IDMS will update the row only if the update operation does not change the values in the referenced columns.
Satisfying Check Constraints
If the updates to a row do not satisfy the check constraints, if any, in the table definition, CA IDMS returns an error and does not update the row.
Updating Through a View
If the target of the update statement is a view, the view must be updateable, and only rows that can be retrieved through the view can be updated through the view.
If the view being updated is defined with WITH CHECK OPTION, any WHERE clause in the view definition, or in the definitions of any other views nested within its definition, will be applied like a check constraint to restrict the update values.
Using a query-expression as a Source Value
If a query-expression used as the value stored in a column returns no rows, the column is set to the null value. If the column does not allow nulls, an exception is raised.
Examples
Requesting a Searched Update
The following UPDATE statement updates the MANAGER_ID column in the EMPLOYEE table for rows where the value in the column currently is 3222:
update employee    set manager_id = 9847    where manager_id = 3222;
Requesting a Positioned Update
The following UPDATE statement updates the BENEFITS table through the BONUS_CURSOR cursor. The statement stores the value in the host variable CALC-BONUS-AMT in the BONUS_AMOUNT column of the table row that corresponds to the current row of the cursor.
EXEC SQL    UPDATE BENEFITS       SET BONUS_AMOUNT = :CALC-BONUS-AMT       WHERE CURRENT OF BONUS_CURSOR END-EXEC
A Positioned UPDATE Referencing a DECLAREd Cursor
The following statement updates the current row of the cursor C1. C1 may be a dynamic or static cursor, but it must have been defined using a DECLARE CURSOR statement. Furthermore, the
cursor-specification
on which C1 is based must contain a FOR UPDATE option which directly or implicitly includes the EMP_LNAME column:
EXEC SQL   UPDATE EMPLOYEE      SET EMP_LNAME = :emp-name      WHERE CURRENT OF C1 END-EXEC
A Positioned UPDATE Referencing an ALLOCATEd Cursor
The following statement updates the current row of a cursor whose name is specified in the variable CNAME. The referenced cursor must have been defined using an ALLOCATE CURSOR statement:
EXEC SQL   UPDATE EMPLOYEE     SET EMP_LNAME = :emp-name     WHERE CURRENT OF GLOBAL :CNAME END-EXEC
A Dynamically-compiled Positioned UPDATE Statement
The following statement updates the current row of local cursor C1. C1 may have been defined using either a DECLARE CURSOR statement or an ALLOCATE CURSOR statement. In either case, the cursor name in the UPDATE statement is specified as an identifier rather than as a literal or host variable:
EXEC SQL   EXECUTE IMMEDIATE   'UPDATE EMPLOYEE SET EMP_STATUS = "T"       WHERE CURRENT OF LOCAL C1' END-EXEC
The keyword LOCAL is unnecessary since it is the default. Regardless of whether it is specified, if two local cursors named C1 have been defined, one using a DECLARE CURSOR statement and one using an ALLOCATE CURSOR statement, the EXECUTE IMMEDIATE statement will fail on an ambiguous cursor error.
Using query-expressions to Update Columns
The following example sets the value of the SALARY_BUDGET column in the DEPARTMENT table based on the current salaries of all employees in the department.
update department d   set salary_budget =      (select 1.1 * sum (salary) from employee e       where e.deptid = d.deptid)
Updating All Rows
The following UPDATE statement modifies every row in the INSURANCE_PLAN table. The statement increases all the values in the FAMILY_COST column by 2 percent and all the values in the DEP_COST column by 1 percent:
update insurance_plan    set family_cost = family_cost * 1.02,       dep_cost = dep_cost * 1.01;
Using Virtual Foreign Keys in an UPDATE to DISCONNECT from a set
The following UPDATE statement sets the FKEY_DEPT_EMPLOYEE column for EMPLOYEE 23 to NULL. Because of the referential relationship between ROWID of DEPARTMENT and FKEY_DEPT_EMPLOYEE defined by the network set DEPT-EMPLOYEE, the record is disconnected from the set occurrence in which it currently participates, if any:
   UPDATE EMPLOYEE SET FKEY_DEPT_EMPLOYEE = NULL WHERE EMP_ID_0415 = 23;
Using Virtual Foreign Keys in an UPDATE to CONNECT to a set
The following UPDATE statement sets the FKEY_DEPT_EMPLOYEE column for EMPLOYEE 23 to the ROWID value of the DEPARTMENT 4000. Because of the referential relationship between ROWID of DEPARTMENT and FKEY_DEPT_EMPLOYEE defined by the network set DEPT-EMPLOYEE, the record is connected into the set occurrence where the owner is DEPARTMENT 4000:
UPDATE EMPLOYEE SET FKEY_DEPT_EMPLOYEE =       (SELECT ROWID        FROM DEPARTMENT        WHERE DEPT_ID_0410 = 4000)   WHERE EMP_ID_0415 = 23;
Using Virtual Foreign Keys in an UPDATE to change set membership
The following UPDATE statement sets the FKEY_DEPT_EMPLOYEE column for EMPLOYEE 23 to the ROWID value of the DEPARTMENT 3200. Because of the referential relationship between ROWID of DEPARTMENT and FKEY_DEPT_EMPLOYEE defined by the network set DEPT-EMPLOYEE, the record is disconnected from DEPARTMENT 4000 and connected into the set occurrence with owner DEPARTMENT 3200:
UPDATE EMPLOYEE     SET FKEY_DEPT_EMPLOYEE =       (SELECT ROWID        FROM DEPARTMENT        WHERE DEPT_ID_0410 = 3200)   WHERE EMP_ID_0415 = 23;
More Information