Oracle8i SQL Reference
Release 2 (8.1.6)






Prev Up Next

SQL Statements (continued), 5 of 5




subquery: see "SELECT and Subqueries".







To change existing values in a table or in a view's base table.


For you to update values in a table, the table must be in your own schema or you must have UPDATE privilege on the table.

For you to update values in the base table of a view,

If the SQL92_SECURITY initialization parameter is set to TRUE, then you must have SELECT privilege on the table whose column values you are referencing (such as the columns in a where_clause) to perform an UPDATE.

The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.

Keywords and Parameters


is a comment that passes instructions to the optimizer on choosing an execution plan for the statement.

You can place a parallel hint immediately after the UPDATE keyword to parallelize both the underlying scan and UPDATE operations.

See Also:




is the schema containing the table or view. If you omit schema, Oracle assumes the table or view is in your own schema.  

table | view | subquery 

is the name of the table or view, or the columns returned by a subquery, to be updated. Issuing an UPDATE statement against a table fires any UPDATE triggers associated with the table. If you specify view, Oracle updates the view's base table.

If table (or the base table of view) contains one or more domain index columns, this statement executes the appropriate indextype update routine.

See Also: Oracle8i Data Cartridge Developer's Guide for more information on these routines. 



  • You cannot execute this statement if table (or the base table of view) contains any domain indexes marked LOADING or FAILED.

  • You cannot specify the order_by_clause in the subquery of the DML_query_expression_clause.



  • You cannot update a view except with INSTEAD OF triggers if the view's defining query contains one of the following constructs:

    - A set operator

    - A DISTINCT operator

    - An aggregate function


    - A collection expression in a SELECT list

    - A subquery in a SELECT list

    - Joins (with some exceptions). See Oracle8i Administrator's Guide for details.

  • If a view was created with the WITH CHECK OPTION, you can update the view only if the resulting data satisfies the view's defining query.



  • If you specify an index, index partition, or index subpartition that has been marked UNUSABLE, the UPDATE statement will fail unless the SKIP_UNUSABLE_INDEXES parameter has been set to TRUE.


PARTITION (partition) | SUBPARTITION (subpartition) 


specifies the name of the partition or subpartition within table targeted for updates. You need not specify the partition name when updating values in a partitioned table. However in some cases specifying the partition name can be more efficient than a complicated where_clause.  


is a complete or partial name of a database link to a remote database where the table or view is located. For information on referring to database links, see "Referring to Objects in Remote Databases". You can use a database link to update a remote table or view only if you are using Oracle's distributed functionality.  


If you omit dblink, Oracle assumes the table or view is on the local database.  


restricts the subquery in one of the following ways: 


  • WITH READ ONLY specifies that the subquery cannot be updated.



  • WITH CHECK OPTION specifies that Oracle prohibits any changes to that table that would produce rows that are not included in the subquery.



informs Oracle that the collection value expression should be treated as a table. You can use a table_collection_expression to update rows in one table based on rows from another table. For example, you could roll up four quarterly sales tables into a yearly sales table.  



is a subquery that selects a nested table column from table or view


Note: In earlier releases of Oracle, table_collection_expr was expressed as "THE subquery". That usage is now deprecated. 



provides a correlation name for the table, view, or subquery to be referenced elsewhere in the statement. 


Note: This alias is required if the DML_query_expression_clause references any object type attributes or object type methods. 



is the name of a column of the table or view that is to be updated. If you omit a column of the table from the set_clause, that column's value remains unchanged.


  • If column refers to a LOB object attribute, you must first initialize it with a value of empty or null. You cannot update it with a literal. Also, if you are updating a LOB value using some method other than a direct UPDATE SQL statement, you must first lock the row containing the LOB.

    See Also: LOB Locking Example.

  • If column is part of the partitioning key of a partitioned table, UPDATE will fail if you change a value in the column that would move the row to a different partition or subpartition, unless you enable row movement.

    See Also: The row_movement_clause of "CREATE TABLE" or "ALTER TABLE".




is a subquery that returns exactly one row for each row updated.

  • If you specify only one column in the set_clause, the subquery can return only one value.

  • If you specify multiple columns in the set_clause, the subquery must return as many values as you have specified columns.

If the subquery returns no rows, then the column is assigned a null.

See Also: "SELECT and Subqueries" and "Using Subqueries".  



Note: If this subquery refers to remote objects, the UPDATE operation can run in parallel as long as the reference does not loop back to an object on the local database. (See the description of the parallel_clause in "CREATE TABLE".) However, if the subquery in the DML_query_expression_clause refers to any remote objects, the UPDATE operation will run serially without notification. 



is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables.

See Also: The syntax description in "Expressions".  



lets you specify the entire row of an object table.

Restriction: You can specify this clause only for an object table.

See Also: "SET VALUE Example"


Note: If you insert string literals into a RAW column, during subsequent queries, Oracle will perform a full table scan rather than using any index that might exist on the RAW column. 


restricts the rows updated to those for which the specified condition is TRUE. If you omit this clause, Oracle updates all rows in the table or view.

The where_clause determines the rows in which values are updated. If you do not specify the where_clause, all rows are updated. For each row that satisfies the where_clause, the columns to the left of the equals (=) operator in the set_clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated.

See Also: The syntax description of "Conditions".  


retrieves the rows affected by the UPDATE statement.  


  • When you are updating a single row, this clause can retrieve column expressions that use the updated columns of the row, rowid, and REFs to the updated row and store them in PL/SQL variables or bind variables.

  • When you are updating multiple rows, this clause can stored the values from expressions, rowid, and REFs involving the updated rows in bind arrays.

  • You can also use UPDATE with a returning_clause to update from views with single base tables.



expr list 

is some of the syntax descriptions in "Expressions". You must specify a column expression in the expr list for each variable in the data_item list. 



indicates that the values of the changed rows are to be stored in the data_item variable(s) specified in data_item list. 



is a PL/SQL variable or bind variable which stores the retrieved expr value in the expr list. 



  • You cannot use this clause with parallel DML or with remote objects.

  • You cannot retrieve LONG types with this clause.

  • You cannot specify this clause for a view on which an INSTEAD OF trigger has been defined.



Simple Examples

The following statement gives null commissions to all employees with the job TRAINEE:

    SET comm = NULL 
    WHERE job = 'TRAINEE'; 

The following statement promotes JONES to manager of Department 20 with a $1,000 raise (assuming there is only one JONES):

    SET job = 'MANAGER', sal = sal + 1000, deptno = 20 
    WHERE ename = 'JONES'; 

The following statement increases the balance of bank account number 5001 in the ACCOUNTS table on a remote database accessible through the database link BOSTON:

UPDATE accounts@boston 
    SET balance = balance + 500 
    WHERE acc_no = 5001; 

The following example updates values in a single partition of the SALES table:

UPDATE sales PARTITION (feb96) s
   SET s.account_name = UPPER(s.account_name);
Complex Example

This example shows the following syntactic constructs of the UPDATE statement:

The above UPDATE statement performs the following operations:


The following statement updates a row of object table TABLE1 by selecting a row from another object table TABLE2:

UPDATE table1 p SET VALUE(p) =
   SELECT VALUE(q) FROM table2 q WHERE =
   WHERE = 10;

The subquery uses the VALUE object reference function in its expression.

Correlated Update Example

The following example updates particular rows of the PROJS nested table corresponding to the department whose department equals 123:

           FROM dept d WHERE d.dno = 123)  p 
  SET p.budgets = p.budgets + 1 
  WHERE p.pno IN (123, 456);

The following example returns values from the updated row and stores the result in PL/SQL variables BND1, BND2, BND3:

  SET job ='MANAGER', sal = sal + 1000, deptno = 20
  WHERE ename = 'JONES'
  RETURNING sal*0.25, ename, deptno INTO bnd1, bnd2, bnd3;

Prev Up Next
Copyright © 1999 Oracle Corporation.

All Rights Reserved.