Updates rows of a table.

Syntax

UPDATE [ optimizer_hint ] table[subquery][@dblink ]
    SET column = { expression | DEFAULT } [, ...]
  [ WHERE condition ]
  [ RETURNING return_expression [, ...]
      { INTO { record | variable [, ...] }
      | BULK COLLECT INTO collection [, ...] } ]

Description

The UPDATE command changes the values of the specified columns in all rows that satisfy the condition. You only need to specify the columns to be modified in the SET clause. Columns that are not specified retain their previous values.

You can specify the RETURNING INTO { record | variable [, ...] clause only within an SPL program. In addition, the result set of the UPDATE command cannot return multiple rows. Otherwise, an exception occurs. If the result set is empty, the content of the target record or variables is set to null.

You can specify the RETURNING BULK COLLECT INTO collection [, ...] clause only if the UPDATE command is used within an SPL program. If you specify multiple collection as the target of the BULK COLLECT INTO clause, each collection must contain a scalar field. collection must not be a record. The result set of the UPDATE command can contain zero, one, or more rows. return_expression that is evaluated for each row of the result set is an element in collection starting from the first element. Existing rows in collection are deleted. If the result set is empty, collection is empty.

To update a table, you must have the UPDATE privilege for the table and the SELECT privilege for all tables whose values are read in expression or condition.

Parameters

Parameter Description
optimizer_hint Comment-embedded hints to the optimizer, which is used to select execution plan.
table The name of the table to be updated. The name can be schema-qualified.
dblink The database link name, which identifies a remote database. For more information about database links, see the CREATE DATABASE LINK command.
column The name of a column in the table.
expression An expression to assign to the column. The expression can use the old values of this column and other columns in the table.
DEFAULT The default expression of the column. If no specific default expression is assigned, the default value is null.
condition An expression that returns a value of the BOOLEAN type. Only rows for which this expression returns true are updated.
return_expression An expression that includes one or more columns from the table. If you specify a column name from the table in return_expression, the value substituted for the column when return_expression is evaluated is determined based on the following rules:
  • If the column specified in return_expression is assigned a value in the UPDATE command, the assigned value is used in the evaluation of return_expression.
  • If the column specified in return_expression is not assigned a value in the UPDATE command, the current value of the column in the affected row is used in the evaluation of return_expression.
record A record that contains fields to which the evaluatedreturn_expression is assigned. For example, the first return_expression is assigned to the first field in record, and the second return_expression is assigned to the second field in record. The number of fields in record must match the number of expressions, and the fields must be type-compatible with their assigned expressions.
variable A variable to which you want to assign the evaluation result of return_expression. If more than one return_expression and variable are specified, the first return_expression is assigned to the first variable and the second return_expression is assigned to the second variable. The number of the specified variables that follow the INTO keyword must match the number of expressions that follow the RETURNING keyword, and the variables must be type-compatible with the corresponding expressions.
collection A collection in which an element is created from the evaluated return_expression. You can specify a collection of a single field or a collection of a record type. You can also specify multiple collections where each collection consists of a single field. The number and order of return expressions must match the number and order of fields in all specified collections. Each return_expression must be type-compatible with the corresponding collection field.
subquery A subquery clause.

Examples

Change the location to AUSTIN for department 20 in the dept table:

UPDATE dept SET loc = 'AUSTIN' WHERE deptno = 20;

For all employees with job = SALESMAN in the emp table, increase the salary by 10% and the commission by 500: 

UPDATE emp SET sal = sal * 1.1, comm = comm + 500 WHERE job = 'SALESMAN';

Take the result of SELECT * FROM table1 as an object, and set the value in the class column of the object to 1. 

UPDATE (SELECT * FROM table1) SET class = 1 WHERE id = 1;