UPDATE

描述

更新与谓词匹配的行的列值。 如果未提供谓词，则更新所有行的列值。

语法

UPDATE table_name [table_alias]
   SET  { { column_name | field_name }  = { expr } [, ...]
   [WHERE clause]

参数

• table_name

  要更新数据的表

  语法: [ database_name. ] table_name

• table_alias

  定义表的别名。 该别名不得包含表中列的列名。

• column_name

  对表中的列的引用。 最多可以引用每列一次。

• field_name

  对 STRUCT 类型的列中的字段的引用。 最多可以引用每字段一次。

• expr

  任意表达式。 如果引用 table_name 列，它们表示更新前的行的状态。

• WHERE

  按谓词筛选要更新的行。

  WHERE 谓词不支持子查询。

示例

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p1|                     1|       1.0|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+


UPDATE t1 SET c3 = 0.5 WHERE c2 = 1;

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p1|                     1|       0.5|
+---------+----------------------+----------+
|       p2|                     2|       2.0|
+---------+----------------------+----------+
|       p3|                     3|       3.0|
+---------+----------------------+----------+


UPDATE t1 SET c3 = c3 * 10 WHERE c2 > 1;

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|       p1|                     1|       0.5|
+---------+----------------------+----------+
|       p2|                     2|      20.0|
+---------+----------------------+----------+
|       p3|                     3|      30.0|
+---------+----------------------+----------+

UPDATE t1 SET c1 = null where c2 = 1；

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|     NULL|                     1|       0.5|
+---------+----------------------+----------+
|       p2|                     2|      20.0|
+---------+----------------------+----------+
|       p3|                     3|      30.0|
+---------+----------------------+----------+

UPDATE t1 SET c2 = 10 where c1 is null；


SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|     NULL|                    10|       0.5|
+---------+----------------------+----------+
|       p2|                     2|      20.0|
+---------+----------------------+----------+
|       p3|                     3|      30.0|
+---------+----------------------+----------+

UPDATE t1 SET c2 = 2, c3 = 3;

SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|     NULL|                     2|       3.0|
+---------+----------------------+----------+
|       p2|                     2|       3.0|
+---------+----------------------+----------+
|       p3|                     2|       3.0|
+---------+----------------------+----------+


UPDATE t1 SET c2 = (SELECT SUM(c2) from c2) WHERE c1 is null;
 
SELECT * FROM t1;
+---------+----------------------+----------+
|       c1|                    c2|        c3|
+---------+----------------------+----------+
|     NULL|                     6|       3.0|
+---------+----------------------+----------+
|       p2|                     2|       3.0|
+---------+----------------------+----------+
|       p3|                     2|       3.0|
+---------+----------------------+----------+

相关文档

INSERT
DELETE
COPY INTO
MERGE INTO