|PL/SQL User's Guide and Reference
Release 2 (9.2)
Part Number A96624-01
PL/SQL Language Elements, 52 of 52
UPDATE statement changes the values of specified columns in one or more rows in a table or view. For a full description of the
UPDATE statement, see Oracle9i SQL Reference.
This is another (usually short) name for the referenced table or view and is typically used in the
This is the name of the column (or one of the columns) to be updated. It must be the name of a column in the referenced table or view. A column name cannot be repeated in the
column_name list. Column names need not appear in the
UPDATE statement in the same order that they appear in the table or view.
This clause lets you return values from updated rows, thereby eliminating the need to
SELECT the rows afterward. You can retrieve the column values into variables and/or host variables, or into collections and/or host arrays. However, you cannot use the
RETURNING clause for remote or parallel updates. For the syntax of
returning_clause, see "DELETE Statement".
This clause assigns the value of
sql_expression to the column identified by
sql_expression contains references to columns in the table being updated, the references are resolved in the context of the current row. The old column values are used on the right side of the equal sign.
In the following example, you increase every employee's salary by 10%. The original value of the
sal column is multiplied by
1.10, then the result is assigned to the
sal column overwriting the original value.
This clause assigns the value retrieved from the database by
subquery3 to the column identified by
column_name. The subquery must return exactly one row and one column.
This clause assigns the values retrieved from the database by
subquery4 to the columns in the
column_name list. The subquery must return exactly one row that includes all the columns listed.
The column values returned by the subquery are assigned to the columns in the column list in order. The first value is assigned to the first column in the list, the second value is assigned to the second column in the list, and so on.
In the following correlated query, the column
item_id is assigned the value stored in
item_num, and the column
price is assigned the value stored in
UPDATE inventory inv -- alias SET (item_id, price) = (SELECT item_num, item_price FROM item_table WHERE item_name = inv.item_name);
This is any valid SQL expression. For more information, see Oracle9i SQL Reference.
This is a
SELECT statement that provides a set of rows for processing. Its syntax is like that of
select_into_statement without the
INTO clause. See "SELECT INTO Statement".
This identifies a table or view that must be accessible when you execute the
UPDATE statement, and for which you must have
UPDATE privileges. For the syntax of
table_reference, see "DELETE Statement".
The operand of
TABLE is a
SELECT statement that returns a single column value, which must be a nested table or a varray. Operator
TABLE informs Oracle that the value is a collection, not a scalar value.
This clause refers to the latest row processed by the
FETCH statement associated with the cursor identified by
cursor_name. The cursor must be
UPDATE and must be open and positioned on a row.
If the cursor is not open, the
OF clause causes an error. If the cursor is open, but no rows have been fetched or the last fetch returned no rows, PL/SQL raises the predefined exception
This clause chooses which rows to update in the database table. Only rows that meet the search condition are updated. If you omit the search condition, all rows in the table are updated.
You can use the
OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor
FOR loop), provided the associated query is
UPDATE. This statement updates the current row, that is, the one just fetched.
The implicit cursor
SQL and the cursor attributes
%ISOPEN let you access useful information about the execution of an
In the following example, a 10% raise is given to analysts in department
In the next example, an employee named Ford is promoted to the position of Analyst and her salary is raised by 15%:
In the final example, values returned from an updated row are stored in variables: