The UPDATE statement is a data manipulation statement that modifies the values in one or more rows of a table.
To issue an UPDATE statement, you must:
Additional authorization requirements apply to:
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.
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.
►►─── 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 ──┘
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:
For expanded table-reference syntax, see Expansion of Table-reference.
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.
Specifies the columns to be updated and the value to be stored in each column.
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.
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.
Directs CA IDMS to store a null value in the named column. The column must be defined to allow null values.
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.
Note: For more information about expanded query-expression syntax, see Chapter 8:.
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.
Specifies criteria a row must meet to be updated:
For expanded search-condition syntax, see Expansion of Search-condition.
Specifies that only the row that corresponds to the current row of the named cursor is to be updated.
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.
Note: This option may only be used in an UPDATE statement embedded in an application program.
Identifies the cursor whose current row will be updated.
Note: 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
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.
Indicates that the named cursor was created by an ALLOCATE CURSOR statement and is global in scope.
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.
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:
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.
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
Note: 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;
|
Copyright © 2014 CA.
All rights reserved.
|
|