UPDATE

The UPDATE statement updates the values of one or more columns in all rows of a table or in rows that satisfy a search condition.

Required privilege

No privilege is required for the table owner.

UPDATE for another user's table.

Usage with TimesTen Scaleout

This statement is supported with TimesTen Scaleout.

SQL syntax

UPDATE [hint] [FIRST NumRows] 
{[Owner.]TableName [CorrelationName]}
SET {ColumnName =
{Expression1 | NULL | DEFAULT}} [,...]
[ WHERE SearchCondition ]
RETURNING|RETURN Expression2[,...] INTO DataItem[,...]

Parameters

Parameter	Description
`hint`	Specifies a statement level optimizer hint for the `UPDATE` statement. See "Statement Level Optimizer Hints" for information on statement level optimizer hints.
`FIRST` `NumRows`	Specifies the number of rows to update. `FIRST` `NumRows` is not supported in subquery statements. `NumRows` must be either a positive `INTEGER` value or a dynamic parameter placeholder. The syntax for a dynamic parameter placeholder is either `?` or :`DynamicParameter`. The value of the dynamic parameter is supplied when the statement is executed.
`[Owner.]TableName` `[CorrelationName]`	`[Owner.]TableName` identifies the table to be updated. `CorrelationName` specifies an alias for the table and must conform to the syntax rules for a basic name. See Basic Names for details. When accessing columns of that table elsewhere in the `UPDATE` statement, use the correlation name instead of the actual table name. The scope of the correlation name is the SQL statement in which it is used. All correlation names within one statement must be unique.
`SET` `ColumnName`	`ColumnName` specifies a column to be updated. You can update several columns of the same table with a single `UPDATE` statement. Primary key columns can be included in the list of columns to be updated as long as the values of the primary key columns are not changed.
`Expression1`	Any expression that does not contain an aggregate function. The expression is evaluated for each row qualifying for the update operation. The data type of the expression must be compatible with the data type of the updated column. `Expression1` can specify a column or sequence `CURRVAL` or `NEXTVAL` reference when updating values. See "Using CURRVAL and NEXTVAL in TimesTen Classic" for more details.
`NULL`	Puts a `NULL` value in the specified column of each row satisfying the `WHERE` clause. The column must allow `NULL` values.
`DEFAULT`	Specifies that the column should be updated with the default value.
`WHERE` `SearchCondition`	The search condition can contain a subquery. All rows for which the search condition is true are updated as specified in the `SET` clause. Rows that do not satisfy the search condition are not affected. If no rows satisfy the search condition, the table is not changed.
`Expression2`	Valid expression syntax. See Expressions for information.
`DataItem`	Host variable or PL/SQL variable that stores the retrieved `Expression2` value.

Description

For TimesTen Scaleout, you cannot update distribution key column(s) unless you update the column(s) to the same value.
You cannot update primary key column(s) unless you update the column(s) to the original value.
If the WHERE clause is omitted, all rows of the table are updated as specified by the SET clause.
TimesTen generates a warning when a character or binary string is truncated during an UPDATE operation.
Constraint violations (UNIQUE, FOREIGN KEY, NOT NULL) result in the failure of the UPDATE statement.
The UPDATE operation fails if it violates any foreign key constraint. See CREATE TABLE for a description of foreign key constraints.
Restrictions on the RETURNING clause:
- Each Expression2 must be a simple expression. Aggregate functions are not supported.
- You cannot return a sequence number into an OUT parameter.
- ROWNUM and subqueries cannot be used in the RETURNING clause.
- Parameters in the RETURNING clause cannot be duplicated anywhere in the UPDATE statement.
- Using the RETURNING clause to return multiple rows requires PL/SQL BULK COLLECT functionality. See FORALL and BULK COLLECT Operations in Oracle TimesTen In-Memory Database PL/SQL Developer's Guide.
- In PL/SQL, you cannot use a RETURNING clause with a WHERE CURRENT operation.

Examples

Use the UPDATE statement to update employees with department_id = 110. For employees with department_id = 110, update the manager_id to the manager_id of employees with job_id = 'FI_ACCOUNT'. Use the DISTINCT qualifier in the subquery of the SET clause.

First find the manager_id of employees with job_id = 'FI_ACCOUNT.'

Command> SELECT manager_id FROM employees WHERE job_id = 'FI_ACCOUNT';
< 108 >
< 108 >
< 108 >
< 108 >
< 108 >
5 rows found.

Next find the manager_id of employees with department_id = 110.

Command> SELECT manager_id FROM employees WHERE department_id = 110;
< 101 >
< 205 >
2 rows found.

Now update the manager_id of employees with department_id = 110. Use SELECT DISTINCT in the subquery of the SET clause. After the UPDATE, verify the manager_id for employees with department_id = 110 was updated.

Command> UPDATE employees SET manager_id =
          (SELECT DISTINCT employees.manager_id
           FROM employees
           WHERE employees.job_id = 'FI_ACCOUNT')
         WHERE employees.department_id = 110;
2 rows updated.

Command> SELECT manager_id FROM employees WHERE department_id = 110;
< 108 >
< 108 >
2 rows found.

Use subqueries in the SET clause of the UPDATE statement. Update employees with location_id = 1700 or location_id = 2400. Set department_id for these employees to the department_id of location_id = 2500. (This is department_id 80). Set salary for these employees to the maximum salary of their department.

First query the first 5 employees to check their department_id and salary.

Command> SELECT FIRST 5 employee_id, department_id, salary
         FROM employees
         ORDER BY employee_id, department_id, salary;
< 100, 90, 24000 >
< 101, 90, 17000 >
< 102, 90, 17000 >
< 103, 60, 9000 >
< 104, 60, 6000 >
5 rows found.

Now use the UPDATE statement to update employees.

Command> UPDATE employees e1
         SET department_id =
              (SELECT department_id FROM departments
               WHERE location_id = 2500),
             salary =
              (SELECT MAX(salary) FROM employees e2
                WHERE e1.department_id = e2.department_id)
         WHERE department_id IN
           (SELECT department_id FROM departments
            WHERE location_id = 2400 OR location_id = 1700);
19 rows updated.

Query the first five employees again to check that employees with the original department_id of 90 have been updated. The department_id is now 80 and the salary is 24000.

Command> SELECT FIRST 5 employee_id, department_id, salary
         FROM employees
         ORDER BY employee_id, department_id, salary;
< 100, 80, 24000 >
< 101, 80, 24000 >
< 102, 80, 24000 >
< 103, 60, 9000 >
< 104, 60, 6000 >
5 rows found.

The following example increases the price of parts costing more than $500 by 25 percent.

UPDATE purchasing.parts
SET salesprice = salesprice * 1.25
WHERE salesprice > 500.00;

This next example updates the column with the NEXTVAL value from sequence seq.

UPDATE student SET studentno = seq.NEXTVAL WHERE name = 'Sally';

The following query updates the status of all the customers who have at least one unshipped order.

UPDATE customers SET customers.status = 'unshipped'
WHERE customers.id = ANY
    (SELECT orders.custid FROM orders
        WHERE orders.status = 'unshipped');

The following statement updates all the duplicate orders, assuming id is not a primary key.

UPDATE orders a SET orders.status = 'shipped'
    WHERE EXISTS (SELECT 1 FROM orders b
        WHERE a.id = b.id AND a.rowid < b.rowid);

This next example updates job_id, salary and department_id for an employee whose last name is'Jones' in the employees table. The values of salary, last_name and department_id are returned into variables.

Command> VARIABLE bnd1 NUMBER(8,2);
Command> VARIABLE bnd2 VARCHAR2(25) INLINE NOT NULL;
Command> VARIABLE bnd3 NUMBER(4);
Command> UPDATE employees SET job_id='SA_MAN', salary=salary+1000,
          department_id=140 WHERE last_name='Jones'
         RETURNING salary*0.25, last_name, department_id
         INTO :bnd1, :bnd2, :bnd3;
1 row updated.
Command> PRINT bnd1 bnd2 bnd3;
BND1                 : 950
BND2                 : Jones
BND3                 : 140

Join Update

TimesTen supports join update statements. A join update can be used to update one or more columns of a table using the result of a subquery.

Syntax

UPDATE [Owner.]TableName
SET ColumnName=Subquery
 [WHERE SearchCondition]

UPDATE [Owner.]TableName
SET (ColumnName[,...])=Subquery
 [WHERE SearchCondition]

Parameters

A join update statement has the following parameters:

Parameter Description

Parameter	Description
`[Owner.]TableName`	Identifies the table to be updated.
`SET (ColumnName[,...])=` `Subquery`	Specifies the column to be updated. You can update several columns of the same table with a single `UPDATE` statement. The `SET` clause can contain only one subquery, although this subquery can be nested. The number of values in the select list of the subquery must be the same as the number of columns specified in the `SET` clause. An error is returned if the subquery returns more than one row for any updated row.
`WHERE` `SearchCondition`	The search condition can contain a subquery. All rows for which the search condition is true are updated as specified in the `SET` clause. Rows that do not satisfy the search condition are not affected. If no rows satisfy the search condition, the table is not changed.

[Owner.]TableName

Identifies the table to be updated.

SET (ColumnName[,...])= Subquery

Specifies the column to be updated. You can update several columns of the same table with a single UPDATE statement. The SET clause can contain only one subquery, although this subquery can be nested.

The number of values in the select list of the subquery must be the same as the number of columns specified in the SET clause. An error is returned if the subquery returns more than one row for any updated row.

WHERE SearchCondition

The search condition can contain a subquery. All rows for which the search condition is true are updated as specified in the SET clause. Rows that do not satisfy the search condition are not affected. If no rows satisfy the search condition, the table is not changed.

Description

The subquery in the SET clause of a join update does not reduce the number of rows from the target table that are to be updated. The reduction must be specified using the WHERE clause. Thus if a row from the target table qualifies the WHERE clause but the subquery returns no rows for this row, this row is updated with a NULL value in the updated column.

Examples

In this example, if a row from t1 has no match in t2, then its x1 value in the first SELECT and its x1 and y1 values in the second SELECT are set to NULL.

UPDATE t1 SET x1=(SELECT x2 FROM t2 WHERE id1=id2);
UPDATE t1 SET (x1,y1)=(SELECT x2,y2 FROM t2 WHERE id1=id2);

In order to restrict the UPDATE statement to update only rows from t1 that have a match in t2, a WHERE clause with a subquery has to be provided as follows.

UPDATE t1 SET x1=(SELECT x2 FROM t2 WHERE id1=id2) 
  WHERE id1 IN (SELECT id2 FROM t2);
UPDATE t1 SET (x1,y1)=(SELECT x2,y2 FROM t2 WHERE id1=id2) 
  WHERE id1 IN (SELECT id2 FROM t2);