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.

SQL syntax

The UPDATE statement has the syntax:

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

Parameters

The UPDATE statement has the parameters:

Description

• 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.

• The target table of the UPDATE statement is designated by TableName.

• A table on which a unique constraint is defined cannot be updated to contain duplicate rows.

• The UPDATE operation fails if it violates any foreign key constraint. See "CREATE TABLE" for a description of the foreign key constraint.

• 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 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

This 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 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 that id is not a primary key:

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

Make changes to job_id, salary and department_id for an employee whose last name is'Jones' in the employees table. Return the values for salary, last_name and department_id 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]

or

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

Parameters

The UPDATE statement has the parameters:

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 done by specifying 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 NULL value in the updated column.

Examples

If a row from t1 has no match in t2, its x1 value in the first select and its x1,y1 values in the second select is 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 to update only rows from t1 that have a match in t2, a where clause with 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);

See also

SELECT