UPDATE 命令

UPDATE [<hint>]

{

<dml_table_expression_clause>

| ONLY ( <dml_table_expression_clause> )

}

[t_alias]

<update_set_clause>

[ <where_clause> ]

[ <returning_clause> ];

关键词	描述
ONLY	用于视图，表示不更新该视图

dml_table_expression_clause

[schema.]

table 
[

PARTITION ( partition )

SUBPARTITION ( subpartition )

@dblink

view

materialized view

[@ dblink]

( subquery [subquery_restriction_clause] )

table_collection_expression

update_set_clause

SET

( column [, column]... ) = ( subquery )

column =

expr

( subquery )

DEFAULT

  [,...]

VALUE ( t_alias ) =

expr

( subquery )

returning_clause

将受影响的记录重新取出。

RETURNING expr [,..] INTO data_item [,...]

关键词	描述
expr	表达式
data_item	本地变量

示例：

UPDATE employees a
SET department_id =
      (SELECT department_id
        FROM departments
        WHERE location_id = '2100'),
    (salary, commission_pct) =
      (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)
FROM employees b
    WHERE a.department_id = b.department_id)
WHERE department_id IN
    (SELECT department_id
      FROM departments
      WHERE location_id = 2900
      OR location_id = 2700);