|Oracle9i SQL Reference
Release 2 (9.2)
Part Number A96540-01
SQL Statements: SAVEPOINT to UPDATE, 8 of 8
UPDATE statement 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:
UPDATEprivilege on the view, and
UPDATEprivilege on the base table.
TABLE system privilege also allows you to update values in any table or any view's base table.
You must also have the
SELECT privilege on the object you want to update if:
SQL92_SECURITYinitialization parameter is set to
UPDATEoperation references table columns (such as the columns in a
Specify 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
ONLY clause applies only to views. Specify
ONLY syntax if the view in the
UPDATE clause is a view that belongs to a hierarchy and you do not want to update rows from any of its subviews.
Specify the schema containing the table or view. If you omit
schema, then Oracle assumes the table or view is in your own schema.
Specify 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, then Oracle updates the view's base table.
table (or the base table of
view) contains one or more domain index columns, then this statement executes the appropriate indextype update routine.
Oracle9i Data Cartridge Developer's Guide for more information on these routines
Specify 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
Specify a complete or partial name of a database link to a remote database where the table or view is located. 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, then Oracle assumes the table or view is on the local database.
"Referring to Objects in Remote Databases" for information on referring to database links
subquery_restriction_clause to restrict the subquery in one of the following ways:
ONLY to indicate that the table or view cannot be updated.
OPTION to indicate that Oracle prohibits any changes to the table or view that would produce rows that are not included in the subquery.
Specify the name of the
OPTION constraint. If you omit this identifier, Oracle automatically assigns the constraint a name of the form
n is an integer that makes the constraint name unique within the database.
table_collection_expression lets you inform Oracle that the value of
collection_expression should be treated as a table for purposes of query and DML operations. The
collection_expression can be a subquery, a column, a function, or a collection constructor. Regardless of its form, it must return a collection value (that is, a value whose type is nested table or varray). This process of extracting the elements of a collection is called collection unnesting.
In earlier releases of Oracle, when
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.
Specify a correlation name (alias) for the table, view, or subquery to be referenced elsewhere in the statement.
This alias is required if the
Restrictions on the dml_table_expression_clause:
table(or the base table of
view) contains any domain indexes marked
order_by_clausein the subquery of the
OFtriggers if the view's defining query contains one of the following constructs:
OPTION, then you can update the view only if the resulting data satisfies the view's defining query.
UNUSABLE, then the
UPDATEstatement will fail unless the
SKIP_UNUSABLE_INDEXESsession parameter has been set to
ALTER SESSION for information on the
update_set_clause lets you set column values.
Specify 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
update_set_clause, then that column's value remains unchanged.
column refers to a LOB object attribute, then 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, then you must first lock the row containing the LOB.
column is part of the partitioning key of a partitioned table, then
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.
In addition, if
column is part of the partitioning key of a list-partitioned table, then
UPDATE will fail if you specify a value for the column that does not already exist in the
partition_value list of one of the partitions.
Specify a subquery that returns exactly one row for each row updated.
update_set_clause, then the subquery can return only one value.
update_set_clause, then the subquery must return as many values as you have specified columns.
You can use the
flashback_clause of within the subquery to update
table with past data.
If the subquery returns no rows, then the column is assigned a null.
Specify an expression that resolves to the new value assigned to the corresponding column.
DEFAULT to set the column to the value previously specified as the default value for the column. If no default value for the corresponding column has been specified, then Oracle sets the column to null.
Restriction on setting default values: You cannot specify
DEFAULT if you are updating a view.
VALUE clause lets you specify the entire row of an object table.
Restriction on the VALUE clause: You can specify this clause only for an object table.
If you insert string literals into a
where_clause lets you restrict the rows updated to those for which the specified
condition is true. If you omit this clause, then Oracle updates all rows in the table or view.
where_clause determines the rows in which values are updated. If you do not specify the
where_clause, then all rows are updated. For each row that satisfies the
where_clause, the columns to the left of the equals (=) operator in the
update_set_clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated.
Chapter 5, "Conditions" for the syntax of
The returning clause retrieves the rows affected by a DML (
DELETE) statement. You can specify this clause for tables and materialized views, and for views with a single base table.
When operating on a single row, a DML statement with a
returning_clause can retrieve column expressions using the affected row, rowid, and
REFs to the affected row and store them in host variables or PL/SQL variables.
When operating on multiple rows, a DML statement with the
returning_clause stores values from expressions, rowids, and
REFs involving the affected rows in bind arrays.
Each item in the
expr list must be a valid expression syntax. All forms are valid except scalar subquery expressions.
INTO clause indicates that the values of the changed rows are to be stored in the variable(s) specified in
data_item is a host variable or PL/SQL variable that stores the retrieved
For each expression in the
RETURNING list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the
returning_clausefor a multitable insert.
LONGtypes with this clause.
OFtrigger has been defined.
See Also: PL/SQL User's Guide and Reference for information on using the
The following statement gives null commissions to all employees with the job
The following statement promotes Douglas Grant to manager of Department 20 with a $1,000 raise:
UPDATE employees SET job_id = 'SA_MAN', salary = salary + 1000, department_id = 120 WHERE first_name||' '||last_name = 'Douglas Grant';
The following statement increases the balance of bank account number 5001 in the
accounts table on a remote database accessible through the database link
The next example shows the following syntactic constructs of the
update_set_clausetogether in a single statement
where_clauseto limit the updated rows
UPDATE employees a SET department_id = (SELECT department_id FROM departments WHERE location_id = '2100'), (salary, commission_pct) = (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct) FROM employees b WHERE a.department_id = b.department_id) WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 2900 OR location_id = 2700);
UPDATE statement performs the following operations:
department_idfor these employees to the
department_idcorresponding to Bombay (
The following example updates values in a single partition of the
The following statement updates a row of object table
table1 by selecting a row from another object table
The subquery uses the
value object reference function in its expression.
The following example updates particular rows of the
projs nested table corresponding to the department whose department equals 123:
UPDATE TABLE(SELECT projs 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
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;