Updates rows of a table.
Syntax
UPDATE [ optimizer_hint ] table[@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 more than one collection is specified as the target of the BULK COLLECT INTO clause, each collection must contain a scalar field. In other words, collection cannot be a record. The result set of the UPDATE command can contain none, 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. All existing rows in collection are deleted. If the result set is empty, collection is also 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. This parameter is used to select an execution plan. |
| table | The name of the table to be updated. The name can be schema-qualified. |
| dblink | The name of the database link. This parameter is used to identify 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 as follows:
|
| record | A record that contains fields to which the evaluated return_expression is assigned. The first return_expression is assigned to the first field in record. The second return_expression is assigned to the second field in record. The number of fields in record must be the same as the number of expressions. The fields must be type-compatible with their assigned expressions. |
| variable | A variable to which the evaluated return_expression is assigned. 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 variables specified following the INTO keyword must be the same as the number of expressions following the RETURNING keyword. The variables must be type-compatible with their assigned expressions. |
| collection | A collection in which an element is created from the evaluated return_expression. One or more collections can exist. A single collection can be a collection of a single field or a collection of a record type. If multiple collections exist, each collection must consist of a single field. The number and sequence of returned expressions must be the same as the number and sequence of fields in all specified collections. Each corresponding return_expression and collection fields must be type-compatible. |
Examples
Run the following command to 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, run the following command to update the salary by 10% and increase the commission by 500:
UPDATE emp SET sal = sal * 1.1, comm = comm + 500 WHERE job = 'SALESMAN';