For you to update values in the base table of a view, the owner of the schema containing the view must have UPDATE privilege on the base table. Also, if the view is in a schema other than your own, you must have UPDATE privilege on the view.
The UPDATE ANY TABLE system privilege also allows you to update values in any table or any view's base table.
If you are using Trusted Oracle7 in DBMS MAC mode, your DBMS label must match the creation label of the table or view:
schema
is the schema containing the table or view. If you omit schema, Oracle7 assumes the table or view is in your own schema.
table view
is the name of the table to be updated. If you specify view, Oracle7 updates the view's base table.
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 the section, "Referring to Objects in Remote Databases," ![[*]](jump.gif)
. You can only use a database link to update a remote table or view if you are using Oracle7 with the distributed option.
If you omit dblink, Oracle7 assumes the table or view is on the local database.
alias
provides a different name for the table, view, or subquery to be referenced elsewhere in the statement.
subquery_1
is a subquery that Oracle treats in the same
manner as a view. For the syntax of subquery,
see .
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.
expr
is the new value assigned to the corresponding column. This expression can contain host variables and optional indicator variables. See the syntax description of expr .
subquery_2
is a subquery that returns new values that are assigned to the corresponding columns. For the syntax of subquery, see .
subquery_3
is a subquery that return a new value that is assigned to the corresponding column. For the syntax of subquery, see .
WHERE
The WHERE clause determines the rows in which values are updated. If the WHERE clause is not specified, 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.
You can use comments in an UPDATE statement to pass instructions, or hints, to the Oracle7 optimizer. The optimizer uses hints to choose an execution plan for the statement. For more information, see Oracle7 Server Tuning.
Issuing an UPDATE statement against a table fires any UPDATE triggers associated with the table.
You cannot update a view if the view's defining query contains one of the following constructs:
The SET clause may mix assignments of expressions and subqueries.
Potentially, each row evaluated by an UPDATE statement could be updated with a different value as determined by the correlated subquery. Normal UPDATE statements update each row with the same value.
Example I
The following statement gives null commissions to all employees with the job TRAINEE:
UPDATE emp
SET comm = NULL
WHERE job = 'TRAINEE'
Example II
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'
Example III
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
Example IV
This example shows the following syntactic constructs of the UPDATE command:
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:
INSERT command