DB2 - Banco de dados - UPDATE - www.cadcobol.com.br
The UPDATE statement updates the values of specified columns in rows of a table, view or nickname, or the underlying tables, nicknames, or views of the specified fullselect.
Updating a row of a view updates a row of its base table, if no INSTEAD OF trigger is defined for the update operation on this view. If such a trigger is defined, the trigger will be executed instead. Updating a row using a nickname updates a row in the data source object to which the nickname refers.
The forms of this statement are:
An UPDATE statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared.
The privileges held by the authorization ID of the statement must include at least one of the following authorities:
If a row-fullselect is included in the assignment, the privileges held by the authorization ID of the statement must include at least one of the following authorities for each referenced table, view, or nickname:
For each table, view, or nickname referenced by a subquery, the privileges held by the authorization ID of the statement must also include at least one of the following authorities:
If the package used to process the statement is precompiled with SQL92 rules (option LANGLEVEL with a value of SQL92E or MIA), and the searched form of an UPDATE statement includes a reference to a column of the table, view, or nickname in the right side of the assignment-clause, or anywhere in the search-condition, the privileges held by the authorization ID of the statement must also include at least one of the following authorities:
If the specified table or view is preceded by the ONLY keyword, the privileges held by the authorization ID of the statement must also include the SELECT privilege for every subtable or subview of the specified table or view.
GROUP privileges are not checked for static UPDATE statements.
If the target of the update operation is a nickname, privileges on the object at the data source are not considered until the statement is executed at the data source. At this time, the authorization ID that is used to connect to the data source must have the privileges that are required for the operation on the object at the data source. The authorization ID of the statement can be mapped to a different authorization ID at the data source.
If table-name is a typed table, rows of the table or any of its proper subtables may get updated by the statement. Only the columns of the specified table may be set or referenced in the WHERE clause. For a positioned UPDATE, the associated cursor must also have specified the same table, view or nickname in the FROM clause without using ONLY.
If the object of the update operation is a fullselect, the fullselect must be updatable, as defined in the Updatable views Notes item in the description of the CREATE VIEW statement.
Updatable views
If the object of the update operation is a nickname, the extended indicator variable values of DEFAULT and UNASSIGNED must not be used (SQLSTATE 22539).
For additional restrictions related to temporal tables and use of a view or fullselect as the target of the update operation, see Considerations for a system-period temporal table and Considerations for an application-period temporal table in the Notes section of this topic.
Considerations for a system-period temporal table
Considerations for an application-period temporal table
For the period specified with FROM value1 TO value2, the BUSINESS_TIME period in a row in the target of the update is in any of the following states:
If the BUSINESS_TIME period in a row is not contained in the specified period, the row is not updated. Otherwise, the update is applied based on how the values in the columns of the BUSINESS_TIME period overlap the specified period as follows:
Assignments and comparisons
Each expression can contain any of the following supported operands (SQLSTATE 428HY):
table-reference
Subselect
If it specifies an INCLUDE column, the column name cannot be qualified.
For a Positioned UPDATE:
Expressions
An expression may contain references to columns of the target table of the UPDATE statement. For each row that is updated, the value of such a column in an expression is the value of the column in the row before the row is updated.
An expression cannot contain references to an INCLUDE column. If expression is a single host variable, the host variable can include an indicator variable that is enabled for extended indicator variables. If extended indicator variables are enabled, the extended indicator variable values of default (-5) or unassigned (-7) must not be used (SQLSTATE 22539) if either of the following statements is true:
ALTER TABLE
The only value that a generated column defined with the GENERATED ALWAYS clause can be set to is DEFAULT (SQLSTATE 428C9).
The DEFAULT keyword cannot be used as the value in an attribute assignment (SQLSTATE 429B9).
The DEFAULT keyword cannot be used as the value in an assignment for update on a nickname where the data source does not support DEFAULT syntax.
A row-fullselect may contain references to columns of the target table of the UPDATE statement. For each row that is updated, the value of such a column in an expression is the value of the column in the row before the row is updated. An error is returned if there is more than one row in the result (SQLSTATE 21000).
Specifies a list of source tables that supply values for assignment to target table columns. The source tables are implicitly inner joined with the target table with the WHERE clause specifying the join condition. The rows in the target table that satisfy the WHERE condition are updated with the values from the source table rows.
When an UPDATE statement specifies a FROM clause:
The search-condition is applied to each row of the table, view or nickname and the updated rows are those for which the result of the search-condition is true.
If the search condition contains a subquery, the subquery can be thought of as being executed each time the search condition is applied to a row, and the results used in applying the search condition. In actuality, a subquery with no correlated references is executed only once, whereas a subquery with a correlated reference may have to be executed once for each row.
DECLARE CURSOR
The specified table, view, or nickname must also be named in the FROM clause of the SELECT statement of the cursor, and the result table of the cursor must not be read-only. (For an explanation of read-only result tables, see DECLARE CURSOR.)
When the UPDATE statement is executed, the cursor must be positioned on a row; that row is updated.
This form of UPDATE cannot be used (SQLSTATE 42828) if the cursor references:
If a view is used that is not defined using WITH CHECK OPTION, rows can be changed so that they no longer conform to the definition of the view. Such rows are updated in the base table of the view and no longer appear in the view.
If a view is used that is defined using WITH CHECK OPTION, an updated row must conform to the definition of the view. For an explanation of the rules governing this situation, see CREATE VIEW.
CREATE VIEW
An UPDATE to a table with check constraints defined has the constraint conditions for each column updated evaluated once for each row that is updated. When processing an UPDATE statement, only the check constraints referring to the updated columns are checked.
The session authorization ID must also have been granted a security label for write access for the security policy if an implicit value is used for a DB2SECURITYLABEL column (SQLSTATE 23523), which can happen when:
If a target column is not updatable (for example, a column in a view that is defined as an expression), then it must be assigned the extended indicator variable-based value of unassigned (SQLSTATE 42808).
If the target column is a column defined as GENERATED ALWAYS, then it must be assigned the DEFAULT keyword, or the extended indicator variable-based values of default or unassigned (SQLSTATE 428C9).
The UPDATE statement must not assign all target columns to an extended indicator variable-based value of unassigned (SQLSTATE 22540).
SET NEW.EMPNO = DEFAULT
INSERT
If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, an underlying target of the UPDATE statement must not be a system-period temporal table (SQLSTATE 51046), and the target of the UPDATE statement must not be a view defined with the WITH CHECK OPTION if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
When a row of a system-period temporal table is updated, the database manager updates the values of the row-begin and transaction-start-ID columns as follows:
If the UPDATE statement has a search condition containing a correlated subquery that references historical rows (explicitly referencing the name of the history table name or implicitly through the use of a period specification in the FROM clause), the old version of the updated rows that are inserted as historical rows (into the history table if any) are potentially visible to update operations for the rows subsequently processed for the statement.
The target of an UPDATE statement cannot be a fullselect that references a view in the FROM clause followed by a period specification for SYSTEM_TIME if both of the following conditions are true (SQLSTATE 51046):
If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, the underlying target (direct or indirect) of the UPDATE statement cannot be a system-period temporal table (SQLSTATE 51046).
If the CURRENT TEMPORAL SYSTEM_TIME special register is set to a non-null value, the target of an UPDATE statement cannot be a view defined with the WITH CHECK OPTION if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
For an update operation, the adjustment only affects the value for the end column corresponding to the row-end column in the history table associated with the system-period temporal table. Take these adjustments into consideration on subsequent references to the table whether there is a search for the transaction start time in the values for the columns corresponding to the row-begin and row-end columns of the period in the associated system-period temporal table.
If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value, the target of the UPDATE statement must not be a view defined with the WITH CHECK option if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
An UPDATE statement for an application-period temporal table that contains a FOR PORTION OF BUSINESS_TIME clause indicates between which two points in time that the specified updates are effective. When FOR PORTION OF BUSINESS_TIME is specified and the period value for a row, specified by the values of the row-begin column and row-end column, is only partially contained in the period specified from value1 up to value2, the row is updated and one or two rows are automatically inserted to represent the portion of the row that is not changed. New values are generated for each generated column in an application-period temporal table for each row that is automatically inserted as a result of an update operation on the table. If a generated column is defined as part of a unique or primary key, parent key in a referential constraint, or unique index, it is possible that an automatic insert will violate a constraint or index in which case an error is returned.
When a row is inserted into an application-period temporal table that has either a primary key or unique constraint with the BUSINESS_TIME WITHOUT OVERLAPS clause defined, or a unique index with the BUSINESS_TIME WITHOUT OVERLAPS clause defined, if the period defined by the begin and end columns of the BUSINESS_TIME period overlap the period defined by the begin and end columns of the BUSINESS_TIME period for another row with the same unique constraint or unique index in the table, an error is returned.
The target of an UPDATE statement cannot be a fullselect that references a view in the FROM clause followed by a period specification for BUSINESS_TIME if both of the following conditions are true (SQLSTATE 51046):
If the CURRENT TEMPORAL BUSINESS_TIME special register is set to a non-null value, the target of an UPDATE statement cannot be a view defined with the WITH CHECK OPTION if the view definition includes a WHERE clause containing one of the following syntax elements (SQLSTATE 51046):
When an application-period temporal table is the target of an UPDATE statement, the value in effect for the CURRENT TEMPORAL BUSINESS_TIME special register is not the null value, and the BUSTIMESENSITIVE bind option is set to YES, the following additional predicates are implicit:
bt_begin <= CURRENT TEMPORAL BUSINESS_TIME AND bt_end > CURRENT TEMPORAL BUSINESS_TIME
UPDATE EMPLOYEE SET JOB = 'LABORER' WHERE EMPNO = '000290'
UPDATE PROJECT SET PRSTAFF = PRSTAFF + 1.5 WHERE DEPTNO = 'D21'
UPDATE EMPLOYEE SET JOB=NULL, SALARY=0, BONUS=0, COMM=0 WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'
This statement could also be written as follows.
UPDATE EMPLOYEE SET (JOB, SALARY, BONUS, COMM) = (NULL, 0, 0, 0) WHERE WORKDEPT = 'E21' AND JOB <> 'MANAGER'
UPDATE (SELECT EMPNO , SALARY , COMM , AVG(SALARY) OVER (PARTITION BY WORKDEPT) , AVG(COMM) OVER (PARTITION BY WORKDEPT) FROM EMPLOYEE E) AS E(EMPNO, SALARY, COMM, AVGSAL, AVGCOMM) SET (SALARY, COMM) = (AVGSAL, AVGCOMM) WHERE EMPNO = '000120'
The previous statement is semantically equivalent to the following statement, but requires only one access to the EMPLOYEE table, whereas the following statement specifies the EMPLOYEE table twice.
UPDATE EMPLOYEE EU SET (EU.SALARY, EU.COMM) = (SELECT AVG(ES.SALARY) , AVG(ES.COMM) FROM EMPLOYEE ES WHERE ES.WORKDEPT = EU.WORKDEPT) WHERE EU.EMPNO = '000120'
EXEC SQL DECLARE C1 CURSOR FOR SELECT * FROM EMPLOYEE FOR UPDATE OF JOB; EXEC SQL OPEN C1; EXEC SQL FETCH C1 INTO ... ; if ( strcmp (change, "YES") == 0 ) EXEC SQL UPDATE EMPLOYEE SET JOB = :newjob WHERE CURRENT OF C1; EXEC SQL CLOSE C1;
Assume that the following types and tables exist:
CREATE TYPE POINT AS (X INTEGER, Y INTEGER) NOT FINAL WITHOUT COMPARISONS MODE DB2SQL
CREATE TYPE CIRCLE AS (RADIUS INTEGER, CENTER POINT) NOT FINAL WITHOUT COMPARISONS MODE DB2SQL
CREATE TABLE CIRCLES (ID INTEGER, OWNER VARCHAR(50), C CIRCLE
The following example updates the CIRCLES table by changing the OWNER column and the RADIUS attribute of the CIRCLE column where the ID is 999:
UPDATE CIRCLES SET OWNER = 'Bruce' C..RADIUS = 5 WHERE ID = 999
The following example transposes the X and Y coordinates of the center of the circle identified by 999:
UPDATE CIRCLES SET C..CENTER..X = C..CENTER..Y, C..CENTER..Y = C..CENTER..X WHERE ID = 999
The following example is another way of writing both of the previous statements. This example combines the effects of both of the previous examples:
UPDATE CIRCLES SET (OWNER,C..RADIUS,C..CENTER..X,C..CENTER..Y) = ('Bruce',5,C..CENTER..Y,C..CENTER..X) WHERE ID = 999
UPDATE DOCUMENTS SET XMLDOC = (SELECT XMLPARSE(DOCUMENT C1 STRIP WHITESPACE) FROM XMLTEXT WHERE TEXTID = '001') WHERE DOCID = '001'
UPDATE PROJECT P SET P.LOCATION = D.LOCATION FROM DEPARTMENT D WHERE P.DEPTNO = D.DEPTNO;
UPDATE PROJECT P SET P.PRSTAFF = S.ACSTAFF FROM (SELECT PROJNO, MAX(ACSTAFF) ACSTAFF FROM PROJACT GROUP BY PROJNO) S WHERE P.PROJNO = S.PROJNO AND P.PROJNAME = 'PAYROLL PROGRAMMING';
UPDATE EMPLOYEE E SET E.WORKDEPT = P.DEPTNO FROM PROJECT P JOIN EMPPROJACT EP ON P.PROJNO = EP.PROJNO WHERE E.EMPNO = EP.EMPNO AND E.FIRSTNME = 'PHILIP' AND E.LASTNAME = 'SMITH';
