PL/SQL User's Guide and Reference

UPDATE Statement

Description

The 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 Oracle7 Server SQL Reference.

Syntax

update_statement ::=

UPDATE {table_reference | (subquery)} [alias]
   SET {  column_name = {sql_expression | (subquery)}
        | (column_name[, column_name]...) = (subquery)}
       [, {  column_name = {sql_expression | (subquery)}
           | (column_name[, column_name]...) = (subquery)}]...
   [WHERE {search_condition | CURRENT OF cursor_name}];

table_reference ::=

[schema_name.]{table_name | view_name}[@dblink_name]

Keyword and Parameter Description

table_reference

This specifies a table or view, which must be accessible when you execute the UPDATE statement, and for which you must have UPDATE privileges.

subquery

This is a select statement that provides a value or set of values to the UPDATE statement. The syntax of subquery is like the syntax of select_into_statement defined in "SELECT INTO Statement"

, except that subquery cannot have an INTO clause.

alias

This is another (usually short) name for the referenced table or view and is typically used in the WHERE clause.

column_name

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.

sql_expression

This is any expression valid in SQL. For more information, see Oracle7 Server SQL Reference.

SET column_name = sql_expression

This clause assigns the value of sql_expression to the column identified by column_name. If 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.1, then the result is assigned to the sal column.

UPDATE emp SET sal = sal * 1.1;

SET column_name = subquery

This clause assigns the value retrieved from the database by subquery to the column identified by column_name. The subquery must return exactly one row and one column.

SET (column_name[, column_name]...) = subquery

This clause assigns the values retrieved from the database by subquery to the columns in the column_name list. The subquery must return exactly one row, which includes all the columns listed in parentheses on the left side of the equal sign.

The column values returned by subquery are assigned to the columns in the column_name list in order. Thus, the first value is assigned to the first column in the column_name list, the second value is assigned to the second column in the column_name 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 item_price:

UPDATE inventory inv  -- alias
   SET (item_id, price) = (SELECT item_num, item_price 
      FROM item_table
      WHERE item_name = inv.item_name);

WHERE search_condition

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.

WHERE CURRENT OF cursor_name

This clause refers to the latest row processed by the FETCH statement associated with the cursor identified by cursor_name. The cursor must be FOR UPDATE and must be open and positioned on a row. If the cursor is not open, the CURRENT 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 NO_DATA_FOUND.

Usage Notes

You can use the UPDATE WHERE CURRENT OF statement after a fetch from an open cursor (this includes implicit fetches executed in a cursor FOR loop), provided the associated query is FOR UPDATE. This statement updates the current row; that is, the one just fetched.

The implicit SQL cursor and the cursor attributes %NOTFOUND, %FOUND, %ROWCOUNT, and %ISOPEN let you access useful information about the execution of an UPDATE statement.

An UPDATE statement might update one or more rows or no rows. If one or more rows are updated, you get the following results:

SQL%NOTFOUND yields FALSE

SQL%FOUND yields TRUE

SQL%ROWCOUNT yields the number of rows updated

If no rows are updated, you get these results:

SQL%NOTFOUND yields TRUE

SQL%FOUND yields FALSE

SQL%ROWCOUNT yields 0

Examples

In the following example, a 10% raise is given to all analysts and clerks in department 20:

UPDATE emp SET sal = sal * 1.10
   WHERE (job = 'ANALYST' OR job = 'CLERK') AND DEPTNO = 20;

In the next example, an employee named Ford is promoted to the position of Analyst and her salary is raised by 15%:

UPDATE emp SET job = 'ANALYST', sal = sal * 1.15
   WHERE ename = 'FORD';