# UPDATE #### 功能描述 更新表中的数据。UPDATE修改满足条件的所有行中指定的字段值，WHERE子句声明条件，SET子句指定的字段会被修改，没有出现的字段则保持原值。 #### 注意事项 - 使用UPDATE请遵循[UPDATE操作规范](https://support.huaweicloud.com/devg-dws/dws_04_0125.html)。 - **权限要求**：要修改表，用户必须对该表有UPDATE权限，对expression或condition条件里涉及到的任何表要有SELECT权限。 - **不允许对表的分布列（distribute column）进行修改**。 - UPDATE操作频繁的表不建议创建为复制表。 - **列存表约束** ： - 列存表，暂时不支持RETURNING子句。 - 列存表不支持结果不确定的更新（non-deterministic update）。试图对列存表用多行数据更新一行时会报错。 - 列存表的更新操作，旧记录空间不会回收，需要执行VACUUM FULL table_name进行清理。 - 对于列存表，支持轻量化UPDATE操作。轻量化UPDATE只重写更新列，减少空间使用量。列存轻量化UPDATE通过GUC参数enable_light_colupdate控制是否开启，默认关闭。 - 列存轻量化UPDATE在以下场景不能使用：更新索引列，更新主键列，更新分区列，更新PCK列和在线扩容，会自动转化为普通UPDATE方式。 - 列存轻量化UPDATE与后台列存AUTOVACUUM并发会小概率报错，可以通过ALTER TABLE设置表级参数enable_column_autovacuum_garbage为off来避免。需要注意的是设置表级参数enable_column_autovacuum_garbage为off会关闭该表的后台列存AUTOVACUUM。 #### 语法格式 ``` UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ * ] [ [ AS ] alias ] [ PARTITION ( partition_name ) | PARTITION FOR ( partition_key_value [, ...] ) ] SET {column_name = { expression | DEFAULT } |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...] [ FROM from_list] [ WHERE condition ] [ RETURNING {* | {output_expression [ [ AS ] output_name ]} [, ...] }]; where sub_query can be: SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ] { * | {expression [ [ AS ] output_name ]} [, ...] } [ FROM from_item [, ...] ] [ WHERE condition ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] ``` #### 参数说明 表1UPDATE参数说明 | 参数 | 描述 | 示例 | |:---|:---|:---| | plan_hint子句 | 以/\*+ \*/的形式在关键字后，用于对指定语句块生成的计划进行hint调优，详细用法请参见[使用Plan Hint进行调优](https://support.huaweicloud.com/devg-dws/dws_04_0454.html)章节。 | - | | table_name | 要更新的表名，可以带上schema名。 | test.table1，其中test为schema名。 | | \[AS\] alias | 为表指定一个简短的别名，方便引用，例如alias。字符串，符合标识符命名规范，详情请参见[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | 更新别名为e的表employees： ``` UPDATE employees AS e SET e.salary = e.salary * 1.1 WHERE e.department = 'technology'; ``` | | partition_name | 分区名称，该子句指定要更新的分区名称对应的数据，适用于范围分区、列表分区等已命名的分区表。仅8.2.1及以上集群版本支持该选项。 | 更新分区名为p1的数据： ``` UPDATE test_range_row PARTITION(p1) SET d = 41; ``` | | partition_key_value | 分区键值，该子句指定要更新的分区键值对应的数据。 通过PARTITION FOR ( partition_key_value \[, ...\] )子句指定的这一组值，可以唯一确定一个分区。 | 更新分区键值为41的数据行，将相应的b值改为20： ``` UPDATE test_range_row PARTITION FOR (41) SET b = 20; ``` | | column_name | 要修改的字段名。 支持使用目标表的表名加字段名来引用这个字段。例如： ``` UPDATE foo SET foo.col_name = 'GaussDB'; ``` 支持使用目标表的别名加字段名来引用这个字段。例如： ``` UPDATE foo AS f SET f.col_name = 'GaussDB'; ``` | - | | expression | 赋予字段的值或表达式。 | 给商品价格打8折： ``` UPDATE products SET price = price * 0.8 WHERE product_id = 101; ``` | | DEFAULT | 用对应字段的缺省值填充该字段。如果没有缺省值，则为NULL。 | 重置非活跃用户的状态和登录次数： ``` UPDATE users SET status = DEFAULT, login_count = DEFAULT WHERE last_login < CURRENT_DATE - INTERVAL '30 days'; ``` | | sub_query | 子查询。使用同一个数据库里的其他表的查询结果，来更新目标表。 其中SELECT子句具体介绍请参考[SELECT](https://support.huaweicloud.com/sqlreference-dws/dws_06_0238.html)。 | 使用子查询更新部门平均工资： ``` UPDATE departments d SET avg_salary = ( SELECT AVG(salary) FROM employees e WHERE e.dept_id = d.dept_id); ``` | | from_list | 另一个表的表达式列表，允许在WHERE条件里关联其他表字段来更新目标表。与在一个SELECT语句的FROM子句里声明表列表类似。 须知： 目标表绝对不能出现在from_list里，除非在使用一个自连接（此时它必须以from_list的别名出现）。 | [示例：使用FROM from_list子句，优先处理VIP等级高的客户的订单] | | condition | 跟在WHERE后面的一个返回boolean类型结果的表达式。只有这个表达式返回true的行才会被更新。 | 只更新数学成绩低于80分的学生： ``` UPDATE student_scores SET math_score = math_score + 5, update_time = NOW() WHERE math_score < 80; ``` | | output_expression | 在所有需要更新的行都被更新之后，用于计算返回值的表达式，便于确认更新结果。 取值范围：使用任何table以及FROM中列出的表的字段。\*表示返回所有字段。 | 更新并返回修改前后的库存信息： ``` UPDATE inventory SET stock = stock - 10 WHERE category = '电子产品' RETURNING item_id AS "商品ID", name AS "商品名称", stock AS "新库存量", stock + 10 AS "原库存量"; ``` | | output_name | 给RETURNING返回的列起的别名，将输出结果优化。字符串，符合标识符命名规范，详情请参见[标识符命名规范](https://support.huaweicloud.com/sqlreference-dws/dws_06_0007.html#ZH-CN_TOPIC_0000001811634529__section1475018612353)。 | - | #### 示例：常用UPDATE场景 创建样例表并插入数据： ``` DROP TABLE IF EXISTS products; CREATE TABLE products ( product_id INT PRIMARY KEY, --商品ID name VARCHAR(100), --商品名称 price DECIMAL(10,2), --商品价格 stock INT, --商品库存 last_updated TIMESTAMP --数据更新时间 ); INSERT INTO products VALUES (101, 'Phone', 2999.00, 50, NULL), (102, 'PC', 5999.00, 30, NULL), (103, 'PAD', 1999.00, 20, NULL); ``` 不含WHERE子句表示直接更新所有记录的值：所有产品价格上浮20%。 ``` UPDATE products SET price = price * 1.2 , last_updated = NOW(); SELECT * FROM products ORDER BY 1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002329531189.png "点击放大") 使用WHERE子句更新指定的数据：将平板电脑的价格上涨500元 。 ``` UPDATE products SET price = price + 500 , last_updated = NOW() WHERE name = 'PAD'; SELECT * FROM products ORDER BY 1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002329412921.png "点击放大") 使用WHERE子句更新特定范围的数据：商品清库存，需要将库存大于40的商品打8折处理。 ``` UPDATE products SET price = price * 0.8 , last_updated = NOW() WHERE stock > 40; SELECT * FROM products ORDER BY 1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002295496092.png "点击放大") 在一个UPDATE命令中更新多个字段，方法是在SET子句中列出更多赋值：调整PC的价格和库存。 ``` UPDATE products SET price = price - 1000 , stock = stock - 10, last_updated = NOW() WHERE name = 'PC'; SELECT * FROM products ORDER BY 1; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002329538117.png "点击放大") #### 示例：更新分区表的指定分区 创建样例表并导入数据： ``` DROP TABLE IF EXISTS test_range_row; CREATE TABLE test_range_row(a int, b int,d int) DISTRIBUTE BY hash(a) PARTITION BY RANGE(d) ( PARTITION p1 values LESS THAN (60), PARTITION p2 values LESS THAN (75), PARTITION p3 values LESS THAN (90), PARTITION p4 VALUES LESS THAN (maxvalue) ); INSERT INTO test_range_row PARTITION(p1) VALUES(55,20,51); INSERT INTO test_range_row PARTITION(p1) VALUES(54,18,59); INSERT INTO test_range_row PARTITION(p2) VALUES(15,18,63); INSERT INTO test_range_row PARTITION(p3) VALUES(12,21,80); INSERT INTO test_range_row PARTITION(p3) VALUES(83,21,85); INSERT INTO test_range_row PARTITION(p4) VALUES(36,21,97); SELECT * FROM test_range_row ORDER BY d; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002329355361.png "点击放大") 更新分区名称为p1的行数据，将d值修改为41。 ``` UPDATE test_range_row PARTITION(p1) SET d = 41; SELECT * FROM test_range_row ORDER BY d; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002295276092.png "点击放大") 从以上结果看，分区为p1，即d\<60的行数据，相应的d值都被修改为41。 继续执行以下SQL，更新分区键值为41的数据行，将相应的b值改为20。 ``` UPDATE test_range_row PARTITION FOR (41) SET b = 20; SELECT * FROM test_range_row ORDER BY d; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002295440492.png "点击放大") #### 示例：使用FROM from_list子句，优先处理VIP等级高的客户的订单 需要更新目标表：订单表orders，但是需要使用FROM子句关联另一张表customers的客户ID字段，来获取客户等级。 创建客户信息表和订单表，包含的字段如下： ``` DROP TABLE IF EXISTS customers; CREATE TABLE customers ( --创建客户信息表 customer_id INT PRIMARY KEY, --客户ID name VARCHAR(100), --客户姓名 vip_level INT --VIP等级 ); DROP TABLE IF EXISTS orders; CREATE TABLE orders ( --创建订单表 order_id INT PRIMARY KEY, --订单号 customer_id INT , --客户ID amount DECIMAL(10,2), --消费金额 status VARCHAR(20) --订单状态 ); ``` 插入测试数据，订单状态均为pending，即三个客户的订单都未处理。 ``` INSERT INTO customers VALUES (1, '张三', 1), (2, '李四', 3), (3, '王五', 2); INSERT INTO orders VALUES (1001, 1, 500.00, 'pending'), (1002, 2, 1200.00, 'pending'), (1003, 3, 800.00, 'pending'); SELECT * FROM orders; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002329391777.png "点击放大") 由于订单较多，现要求优先处理VIP等级高的客户的订单，此时需要通过FROM from_list子句，关联客户表的客户ID，获取客户VIP等级，本例为，优先处理VIP等级为3级以上的客户订单。 ``` UPDATE orders o SET status = 'priority' --将订单状态修改为“高优先级” FROM customers c WHERE o.customer_id = c.customer_id AND c.vip_level >= 3; --从客户表中获取等级VIP为3级以上的客户 SELECT * FROM orders; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002329395149.png "点击放大") #### 示例：使用DEFAULT重置非活跃用户的状态 创建用户表（带默认值），并插入数据： ``` DROP TABLE IF EXISTS users; CREATE TABLE users ( user_id INT PRIMARY KEY, --用户ID username VARCHAR(50) , --用户名 login_count INT DEFAULT 0, --登录次数，默认为0 last_login TIMESTAMP, --最后一次登录的时间 status VARCHAR(20) DEFAULT 'active'); --用户状态，默认为active INSERT INTO users (user_id, username, login_count, last_login) VALUES (1, 'user1', 5, '2023-06-01 10:00:00'), (2, 'user2', 3, '2023-06-15 14:30:00'); SELECT * FROM users; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002295502584.png "点击放大") 重置非活跃用户的状态和登录次数： ``` UPDATE users SET status = DEFAULT, -- 重置为'active' login_count = DEFAULT -- 重置为0 WHERE last_login < CURRENT_DATE - INTERVAL '30 days'; SELECT * FROM users; ``` ![](https://support.huaweicloud.com/sqlreference-dws/figure/zh-cn_image_0000002329541861.png "点击放大") #### 常见问题 - [执行SQL时报错：abort transaction due to concurrent update](https://support.huaweicloud.com/trouble-dws/dws_09_0206.html) - [如何判断表是否执行过UPDATE或DELETE](https://support.huaweicloud.com/trouble-dws/dws_09_0098.html) - [更新报错ERROR：Non-deterministic UPDATE](https://support.huaweicloud.com/trouble-dws/dws_09_0129.html)