Oracle9i SQL Reference Release 1 (9.0.1) Part Number A90125-01 |
|
SQL Statements:
SAVEPOINT to UPDATE, 9 of 9
Use the 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:
UPDATE
privilege on the view, and
UPDATE
privilege on the base table.
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.
update::=
update
dml_table_expression_clause::=
dml_table_expression_clause
subquery_restriction_clause::=
subquery_restriction_clause
table_collection_expression::=
table_collection_expression
update_set_clause
where_clause
returning_clause
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 UPDATE
operations.
See Also:
|
The 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, 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, 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.
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 where_clause.
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, Oracle assumes the table or view is on the local database.
Use the subquery_restriction_clause to restrict 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.
t
able_collection_expression
The 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 built-in 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.
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.
Restrictions on the dml_table_expression_clause
IN_PROGRESS
or FAILED
.
UNUSABLE
.
INSTEAD
OF
triggers if the view's defining query contains one of the following constructs:
DISTINCT
operator
GROUP
BY
, ORDER
BY
, CONNECT
BY
, or START
WITH
clause
SELECT
list
SELECT
list
WITH
CHECK
OPTION
, you can update the view only if the resulting data satisfies the view's defining query.
UNUSABLE
, the UPDATE
statement will fail unless the SKIP_UNUSABLE_INDEXES
session parameter has been set to TRUE
.
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, 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.
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.
In addition, if column is part of the partitioning key of a list-partitioned table, 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.
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.
Specify 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, Oracle sets the column to null.
Restriction: You cannot specify DEFAULT
if you are updating a view.
The VALUE
clause lets you specify the entire row of an object table.
Restriction: You can specify this clause only for an object table.
The where_clause lets you restrict 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 update_set_clause are set to the values of the corresponding expressions on the right. The expressions are evaluated as the row is updated.
The returning clause retrieves the rows affected by a DML (INSERT
, UPDATE
, or 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.
The INTO
clause indicates that the values of the changed rows are to be stored in the variable(s) specified in data_item
list.
Each data_item
is a host variable or PL/SQL variable that stores the retrieved expr
value.
For each expression in the RETURNING
list, you must specify a corresponding type-compatible PL/SQL variable or host variable in the INTO
list.
Restrictions:
returning_clause
for a multitable insert.
LONG
types with this clause.
INSTEAD
OF
trigger has been defined.
See Also: PL/SQL User's Guide and Reference for information on using the
BULK
COLLECT
clause to return multiple values to collection variables
The following statement gives null commissions to all employees with the job sa_clerk
:
UPDATE employees SET commission_pct = NULL WHERE job = 'SA_CLERK';
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 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 (sales_q1_1999) s SET s.promo_id = 494;
The next example shows the following syntactic constructs of the UPDATE
statement:
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);
The above UPDATE
statement performs the following operations:
department_id
for these employees to the department_id
corresponding to Bombay (location_id
2100)
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 p.id = q.id) WHERE p.id = 10;
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 bnd1
, bnd2
, bnd3
:
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;
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|