UPDATE-PolarDB(PolarDB)-阿里云帮助中心

Updates rows in 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

UPDATE changes the values of the specified columns in all rows that satisfy the WHERE clause condition. List only the columns to modify in the SET clause — columns not listed retain their current values.

Updating via a subquery

You can use a subquery as the update target instead of a table name. The subquery result is treated as the target object, and the SET clause operates on its columns.

RETURNING INTO clause

The RETURNING INTO { record | variable [, ...] } clause is available only inside an SPL (Stored Procedure Language) program. The UPDATE result set must return at most one row; if multiple rows match, an exception is raised. If no rows match, the target record or variables are set to null.

RETURNING BULK COLLECT INTO clause

The RETURNING BULK COLLECT INTO collection [, ...] clause is available only inside an SPL program. The result set can contain zero, one, or more rows. Each evaluated return_expression becomes an element in collection, starting from the first element. Existing elements in collection are deleted before new results are written. If the result set is empty, collection is also empty.

When multiple collections are specified as the BULK COLLECT INTO target, each collection must hold scalar values — collections of record type are not allowed.

Prerequisites

UPDATE privilege on the table being updated.
SELECT privilege on any table whose values are read in an expression or condition.

Parameters

Parameter	Description
`optimizer_hint`	A comment-embedded hint to the optimizer for selecting an execution plan.
`table`	The name of the table to update. The name can be schema-qualified.
`subquery`	A subquery whose result set is used as the update target.
`dblink`	A database link name that identifies a remote database. For more information, see the `CREATE DATABASE LINK` command.
`column`	A column in the table.
`expression`	An expression to assign to the column. The expression can reference the current values of this column and other columns in the table.
`DEFAULT`	Assigns the column's default value. If no default is defined, the column is set to null.
`condition`	An expression that returns a Boolean value. Only rows for which the expression evaluates to true are updated.
`return_expression`	An expression that includes one or more columns from the table. When a column is referenced in `return_expression`, the value used is determined as follows: if the column is assigned a new value in the SET clause, the assigned value is used; if the column is not assigned a new value, the column's current value in the affected row is used.
`record`	A record whose fields receive the evaluated `return_expression` values in order. The number of fields must match the number of return expressions, and each field must be type-compatible with its corresponding expression.
`variable`	A variable to receive the evaluation result of `return_expression`. When multiple return expressions and variables are specified, they are assigned in order. The number of variables following INTO must match the number of expressions following RETURNING, and each variable must be type-compatible with its corresponding expression.
`collection`	A collection in which an element is created from each evaluated `return_expression`. The collection can consist of a single field or a record type. Multiple single-field collections are also supported. The number and order of return expressions must match the number and order of fields across all specified collections, and each expression must be type-compatible with its corresponding collection field.

Usage notes

The RETURNING INTO clause raises an exception if the UPDATE matches more than one row. Use RETURNING BULK COLLECT INTO when the result set may contain multiple rows.
When a subquery is used as the update target, make sure the subquery produces a well-defined set of rows for the columns being updated.

Examples

Update a single column

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

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

Update multiple columns

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';

Update via a subquery

Use the result of SELECT * FROM table1 as the update target, and set the class column to 1 for the row where id = 1:

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