UPDATE

对于满足指定条件的所有行，替换其指定列的值。表中所有其他列和行不变。若成功，UPDATE 将返回被更新的行数。为 0 的计数表示没有任何行与该条件匹配。

语法

UPDATE [[database.]schema.]table-reference [AS] alias 
    SET set‑expression [,...]
    [ FROM from-list ]
    [ where‑clause ]

参数

[database.]schema

数据库和架构。默认架构为 public。如果指定一个数据库，它必须是当前数据库。

table‑reference
一个表，为以下几项之一：

  • 视情况进行限定的表名，带有可选表别名、列别名和外联接。

  • 外联接表。

您无法更新投影。

别名
用于引用表的临时名称。
SET set‑expression
要使用一个或多个 SET 表达式更新的列。每个 SET 子句表达式指定一个目标列及其新值，如下所示： 
column-name =  { expression | DEFAULT }

其中：

  • column-name 是不含主键或外键 引用完整性约束的任意列，而且不是复杂类型。允许使用原生数组。

  • expression 指定要分配给列的值。该表达式可使用此表列或其他表列的当前值。例如：

    => UPDATE T1 SET C1 = C1+1

  • DEFAULT 将 column-name 设置为其默认值，或者如果没有为该列定义默认值，则将其忽略。

UPDATE 只修改通过 SET 子句指定的列。未指定的列保持不变。

FROM from‑list
表的表达式列表，允许其他表的列出现在 WHERE 条件和 UPDATE 表达式中。这类似于可在 SELECT 命令的 FROM 子句 中予以指定的表列表。

FROM 子句可以引用目标表，如下所示：

FROM DEFAULT [join-type] JOIN table-reference [ ON join-predicate ]

DEFAULT 指定要更新的表。此关键字只能在 FROM 子句中使用一次，且不能在 UPDATE 语句的其他位置使用。

特权

表所有者或用户 WITH GRANT OPTION 是授予者。

  • 对表的 UPDATE 权限

  • 包含表的架构上的 USAGE 权限

  • 执行在 WHERE 或 SET 子句中引用表列值的 UPDATE 语句时，对于表拥有 SELECT 权限

子查询和联接

UPDATE 支持子查询和联接，这有利于根据存储在其他表中的值来更新表中的值。有关详细信息，请参阅UPDATE 和 DELETE 语句中的子查询。

Vertica 采用 SQL-92 事务模型，因此连续的 INSERT、UPDATE 和 DELETE 语句包含在同一个事务中。您无需显式启动此事务，但必须使用 COMMIT 显式结束它，或者使用 COPY 隐式结束它。否则，Vertica 将放弃在该事务中所做的所有更改。

限制

  • 不能更新不可变表。

  • 不能更新复杂类型的列，原生数组除外。

  • 如果 FROM 子句或 WHERE 谓词中指定的联接生成多个目标表中行的副本，则将任意选择表中该行的新值。

  • 如果在目标表中启用了主键、唯一键或检查约束以自动强制实施，Vertica 会在您加载新数据时强制实施这些约束。如果发生违规，Vertica 会回滚操作并返回错误。

  • 如果更新会违反表或架构磁盘配额，则操作将失败。有关详细信息，请参阅磁盘配额。

示例

在 fact 表中，对于 cost 列值大于 100 的所有行，修改其 price 列值：

=> UPDATE fact SET price = price - cost * 80 WHERE cost > 100;

在 retail.customer 表中，若 CID 列值大于 100，则将 state 列设置为 NH：

=> UPDATE retail.customer SET state = 'NH' WHERE CID > 100;

要在 UPDATE 查询中使用表别名，请考虑以下两个表：

=> SELECT * FROM result_table;
 cust_id |      address
---------+--------------------
      20 | Lincoln Street
      30 | Beach Avenue
      30 | Booth Hill Road
      40 | Mt. Vernon Street
      50 | Hillside Avenue
(5 rows)
=> SELECT * FROM new_addresses;
 new_cust_id | new_address
-------------+---------------
          20 | Infinite Loop
          30 | Loop Infinite
          60 | New Addresses
(3 rows)

以下查询和子查询使用表别名将 result_table（别名 address）中的 r 列更新为 new_addresses 表（别名 n）中相应列的新地址：

=> UPDATE result_table r
   SET address=n.new_address
   FROM new_addresses n
   WHERE r.cust_id = n.new_cust_id;

result_table 显示了顾客 ID 20 和 30 的 address 字段更新：

=> SELECT * FROM result_table ORDER BY cust_id;
 cust_id |     address
---------+------------------
      20 | Infinite Loop
      30 | Loop Infinite
      30 | Loop Infinite
      40 | Mt. Vernon Street
      50 | Hillside Avenue
(5 rows)

不能使用 UPDATE 更新原生数组的单个元素。只能替换整个数组值。以下示例使用 ARRAY_CAT 将元素添加到数组列：

=> SELECT * FROM singers;
 lname  | fname |                    bands
--------+-------+---------------------------------------------
 Cher   |       | ["Sonny and Cher"]
 Jagger | Mick  | ["Rolling Stones"]
 Slick  | Grace | ["Jefferson Airplane","Jefferson Starship"]
(3 rows)

=> UPDATE singers SET bands=ARRAY_CAT(bands,ARRAY['something new'])
    WHERE lname='Cher';
 OUTPUT
--------
      1
(1 row)

=> SELECT * FROM singers;
 lname  | fname |                    bands
--------+-------+---------------------------------------------
 Jagger | Mick  | ["Rolling Stones"]
 Slick  | Grace | ["Jefferson Airplane","Jefferson Starship"]
 Cher   |       | ["Sonny and Cher","something new"]
(3 rows)