Oracle8i SQL Reference Release 2 (8.1.6) A76989-01 |
|
SQL Statements (continued), 5 of 5
DML_table_expression_clause::=
subquery: see "SELECT and Subqueries".
table_collection_expression::=
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.
hint |
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 See Also:
|
|
DML_table_expression_clause |
||
schema |
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 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. |
|
|
Restrictions: |
|
|
|
|
|
See Also: "ALTER SESSION" |
|
|
||
|
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. |
|
dblink |
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. |
|
with_clause |
restricts the subquery in one of the following ways: |
|
|
||
|
See Also: WITH CHECK OPTION Example. |
|
table_collection_expression |
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. |
|
|
collection_expression |
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 " |
|
|
||
t_alias |
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. |
|
set_clause |
column |
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. Restrictions:
|
|
subquery |
is a subquery that returns exactly one row for each row updated.
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 |
|
expr |
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 |
|
where_clause |
restricts the rows updated to those for which the specified condition is 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". |
|
returning_clause |
retrieves the rows affected by the |
|
|
|
|
|
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. |
|
data_item |
is a PL/SQL variable or bind variable which stores the retrieved expr value in the expr list. |
|
Restrictions: |
The following statement gives null commissions to all employees with the job TRAINEE
:
UPDATE emp 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
):
UPDATE emp 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);
This example shows the following syntactic constructs of the UPDATE
statement:
UPDATE emp a SET deptno = (SELECT deptno FROM dept WHERE loc = 'BOSTON'), (sal, comm) = (SELECT 1.1*AVG(sal), 1.5*AVG(comm) FROM emp b WHERE a.deptno = b.deptno) WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'DALLAS' OR loc = 'DETROIT');
The above UPDATE
statement performs the following operations:
DEPTNO
for these employees to the DEPTNO
of Boston
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 emp SET job ='MANAGER', sal = sal + 1000, deptno = 20 WHERE ename = 'JONES' RETURNING sal*0.25, ename, deptno INTO bnd1, bnd2, bnd3;
|
Copyright © 1999 Oracle Corporation. All Rights Reserved. |
|