Oracle7 Server SQL Reference

UPDATE (Embedded SQL)

Purpose

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

Prerequisites

For you to update values in a table or snapshot, 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, 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:

If the creation label of the table or view is higher than your DBMS label, you must have READUP and WRITEUP system privileges

If the creation label of the table or view is lower than your DBMS label, you must have WRITEDOWN system privilege.

If the creation label of your table or view is not comparable to your DBMS label, you must have READUP, WRITEUP, and WRITEDOWN system privileges.

Syntax

Keywords and Parameters

identifies the database to which the UPDATE statement is issued. The database can be identified by either:

db_name is a database identifier declared in a previous DECLARE DATABASE statement.

:host_variable is a host variable whose value is a previously declared db_name.

If you omit this clause, the UPDATE statement is issued to your default database.

FOR :host_integer

limits the number of times the UPDATE statement is executed if the SET and WHERE clauses contain array host variables. If you omit this clause, Oracle7 executes the statement once for each component of the smallest array.

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," . You can only use a database link to update a remote table or view if you are using Oracle7 with the distributed option.

subquery_1

is a subquery that Oracle treats in the same manner as a view. For the syntax of subquery, see .

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

t_alias

is a name used to reference the table, view, or subquery elsewhere in the statement.

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

specifies which rows of the table or view are updated:

condition updates only rows for which this condition is true. This condition can contain host variables and optional indicator variables. See the syntax description of condition .

CURRENT OF updates only the row most recently fetched by the cursor. The cursor cannot be associated with a SELECT statement that performs a join unless its FOR UPDATE clause explicitly locks only one table.

If you omit this clause entirely, Oracle7 updates all rows of the table or view.

Usage Notes

Host variables in the SET and WHERE clauses must be either all scalars or all arrays. If they are scalars, Oracle7 executes the UPDATE statement only once. If they are arrays, Oracle7 executes the statement once for each set of array components. Each execution may update zero, one, or multiple rows.

Array host variables can have different sizes. In this case, the number of times Oracle7 executes the statement is determined by the smaller of the following values:

the size of the smallest array

the value of the :host_integer in the optional FOR clause

The cumulative number of rows updated is returned through the third element of the SQLERRD component of the SQLCA. When arrays are used as input host variables, this count reflects the total number of updates for all components of the array processed in the UPDATE statement. If no rows satisfy the condition, no rows are updated and Oracle7 returns an error message through the SQLCODE element of the SQLCA. If you omit the WHERE clause, all rows are updated and Oracle7 raises a warning flag in the fifth component of the SQLWARN element of the SQLCA.

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 on hints, see Oracle7 Server Tuning.

For more information on this command, see Programmer's Guide to the Oracle Precompilers.

Examples

The following examples illustrate the use of the embedded SQL UPDATE command:

EXEC SQL UPDATE emp

	SET sal = :sal, comm = :comm INDICATOR :comm_ind

	WHERE ename = :ename;

EXEC SQL UPDATE emp

	SET (sal, comm) =

		(SELECT AVG(sal)*1.1, AVG(comm)*1.1

			FROM emp)