更新时间：2024-08-20 GMT+08:00
UPDATE
功能描述
更新表中的数据，UPDATE对满足条件的所有行中指定的字段值进行修改；WHERE子句表示声明条件；SET子句中指定的字段将会被修改，没有出现的字段则保持原有的字段值。
注意事项
- 表的所有者、拥有表UPDATE权限的用户或拥有UPDATE ANY TABLE权限的用户，皆有权限更新表中的数据，当三权分立开关关闭时，系统管理员默认拥有此权限。
- UPDATE...... LIMIT row_count 仅支持执行计划下推的部分场景（不支持复制表），前置条件要求过滤条件包含等值分布列且过滤条件相对简单，避免出现强制类型转换的情况。如果执行失败，请简化过滤条件。
- 对expression或condition条件里涉及到的任何表要有SELECT权限。
- 仅当表属性enable_update_distkey设置为on，且在约束条件下时，支持更新（UPDATE）分布列的操作。否则，分布列不支持更新（UPDATE）操作。
支持更新（UPDATE）分布列操作的约束如下：
- 仅当相应表属性enable_update_distkey设置为on时才支持更新分布列；
- 不支持将UPDATE语句下推DN执行，直接生成PGXC计划，不会根据更新分布列前后值而对计划做改变；
- 不支持带有行级UPDATE TRIGGER的表，否则会执行失败，报错进行提示。对行级INSERT/DELETE TRIGGER不生效，update statement级TRIGGER正常执行；
- 不支持并发更新同一行，先获取锁的执行，DN上后获取锁的按照GUC参数concurrent_dml_mode设置情况进行不同的行为（返回0或报错）。如果报错，则可能存在两种情况：（1）报错提示信息为update distribute column conflict；（2）当获取锁时间超过阈值时，报错提示信息为锁超时。
- 不支持带有全局二级索引（GSI）的表，否则会执行报错；
- 只支持HASH分布，不支持LIST/RANGE分布表，否则会执行报错；
- 不支持MERGE INTO和UPSERT更新分布列的行为，否则会执行报错；
- 不支持gtm_free，否则会执行报错；
- 不支持UPDATE RETURNING，否则会执行报错；
- 不支持带有关联表的语句，否则会执行报错；
- 不支持UPDATE + LIMIT，否则会执行报错。
- 对于子查询是STREAM计划的UPDATE语句，不支持并发更新同一行。
- 不支持用户通过UPDATE系统表的方式对数据库字符编码进行修改，该操作会导致存在存量数据或其他部分操作异常的情况。如需更改数据库的字符集编码，应当遵循切换数据库流程，进行相关的数据迁移操作。
语法格式
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
[ WITH [ RECURSIVE ] with_query [, ...] ] UPDATE [/*+ plan_hint */] [ ONLY ] {table_name [ partition_clause ] | subquery | view_name} [ * ] [ [ AS ] alias ] SET {column_name = { expression | DEFAULT } |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...] [ FROM from_list] [ WHERE condition | WHERE CURRENT OF cursor_name ] [ ORDER BY {expression [ [ ASC | DESC | USING operator ] [ LIMIT row_count ] [ 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 | WHERE CURRENT OF cursor_name ] [ GROUP BY grouping_element [, ...] ] [ HAVING condition [, ...] ] [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ] [ LIMIT { [offset,] count | ALL } ] |
参数说明
- WITH [ RECURSIVE ] with_query [, ...]
用于声明一个或多个可以在主查询中通过名称引用的子查询，相当于临时表。这种子查询语句结构称为CTE（Common Table Expression）结构，应用这种结构时，执行计划中将存在CTE SCAN的内容。
如果声明了RECURSIVE，那么允许SELECT子查询通过名称引用它自己。
其中with_query的详细格式为：
with_query_name [ ( column_name [, ...] ) ] AS [ [ NOT ] MATERIALIZED ] ( {select | values | insert | update | delete} )- with_query_name指定子查询生成的结果集名称，在查询中可使用该名称访问子查询的结果集。
- column_name指定子查询结果集中显示的列名。
- 每个子查询可以是SELECT、VALUES、INSERT、UPDATE或DELETE语句。
- 用户可以使用MATERIALIZED或NOT MATERIALIZED对CTE进行修饰。
- 如果声明为MATERIALIZED，WITH查询将被物化，生成一个子查询结果集的复制，在引用处直接查询该复制，因此WITH子查询无法和主干SELECT语句进行联合优化（如谓词下推、等价类传递等），对于此类场景可以使用NOT MATERIALIZED进行修饰，如果WITH查询语义上可以作为子查询内联执行，则可以进行上述优化。
- 如果用户没有显示声明物化属性则遵守以下规则：如果CTE只在所属SELECT主干中被引用一次，且语义上支持内联执行，则会被改写为子查询内联执行，否则以CTE Scan的方式物化执行。
- plan_hint
以/*+ */的形式在UPDATE关键字后，用于对UPDATE对应的语句块生成的计划进行hint调优，详细用法请参见章节使用Plan Hint进行调优。每条语句中只有第一个/*+ plan_hint */注释块会作为hint生效，里面可以写多条hint。
- table_name
要更新的表名，可以使用模式修饰。如果在表名前指定了ONLY，只会更新表中匹配的行。如果未指定，任何从该表继承到的表中的匹配行也会被更新。
取值范围：已存在的表名称。
- subquery
要更新的子查询，在对子查询进行更新时，会将子查询当成一个临时视图，支持在子查询后面加CHECK OPTION选项。
[ WITH [ RECURSIVE ] with_query [, ...] ] SELECT [/*+ plan_hint */] [ ALL ] { * | {expression [ [ AS ] output_name ]} [, ...] } [ into_option ] [ FROM from_item [, ...] ] [ WHERE condition ] [ [ START WITH condition ] CONNECT BY [NOCYCLE] condition [ ORDER SIBLINGS BY expression ] ] [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ] [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ] [ into_option ];其中指定子查询源from_item为：{[ ONLY ] {table_name | view_name} [ * ] [ partition_clause ] [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |( select ) [ AS ] alias [ ( column_alias [, ...] ) ] |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ] |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}如果子查询中只有一张表，则对该表更新数据；如果子查询中有多张表或有嵌套关系，则通过判断是否有保留键表确定是否可更新。关于保留键表和WITH CHECK OPTION请参见CREATE VIEW。
- view_name
要更新的目标视图。
对视图和子查询的更新，有如下约束：
- 只有直接引用基表用户列的列可进行UPDATE操作。
- 子查询或视图必须至少包含一个可更新列，关于可更新列请参见CREATE VIEW。
- 不支持在顶层包含DISTINCT、GROUP BY、HAVING、LIMIT、OFFSET子句的视图和子查询。
- 不支持在顶层包含集合运算（UNION、INTERSECT、EXCEPT、MINUS）的视图和子查询。
- 不支持目标列表中包含聚集函数、窗口函数、返回集合函数（array_agg、json_agg、generate_series等）的视图和子查询。
- 不支持仅带有BEFORE/AFTER触发器，没有INSTEAD OF触发器或INSTEAD规则的视图。
- 视图和子查询中支持的表类型包括普通表、临时表、全局临时表、分区表、二级分区表、ustore表、astore表。
- 多表连接视图或连接子查询中一次只能更新一张基表。
- 连接视图或子查询只能更新保留键表，如果指定了CHECK OPTION选项，则无法对连接列做更新操作。关于保留键表请参见CREATE VIEW。
- 不支持更新系统视图。
- partition_clause
指定分区更新操作。
PARTITION { ( partition_name [, ...] ) | FOR ( partition_value [, ...] ) }
PARTITION指定多个分区名时，可以存在相同的分区名，最终分区范围取其并集。
- alias
目标表的别名。
取值范围：字符串，符合标识符命名规范。
- column_name
要修改的字段名。
支持使用目标表的别名加字段名来引用这个字段。例如：UPDATE foo AS f SET f.col_name = 'namecol'。
取值范围：已存在的字段名。
- expression
赋给字段的值或表达式。
- DEFAULT
用对应字段的缺省值填充该字段。
如果没有缺省值，则为NULL。
- sub_query
子查询。
使用同一数据库里其他表的信息来更新一个表可以使用子查询的方法。其中SELECT子句具体介绍请参考SELECT。
在update单列时，支持使用order by子句与limit子句；而在update多列时，则不支持使用order by子句与limit子句。
- from_list
一个表的表达式列表，允许在WHERE条件里使用其他表的字段。与在一个SELECT语句的FROM子句里声明表列表类似。
目标表不能出现在from_list里，除非在使用一个自连接（此时它必须以from_list的别名出现）。
- condition
一个返回Boolean类型结果的表达式。只有这个表达式返回true的行才会被更新。不建议使用int等数值类型作为condition，因为int等数值类型可以隐式转换为Boolean值（非0值隐式转换为true，0转换为false），可能导致非预期的结果。
- WHERE CURRENT OF cursor_name
当cursor指向表的某一行时，可以使用此语法更新cursor当前指向的行。
cursor_name：指定游标的名称。
- MYSQL兼容模式的数据库不支持使用此语法。
- 此语法仅支持普通表，不支持分区表，不支持Hash Bucket表。
- 仅支持在存储过程中使用。
- 不支持与其他WHERE条件组合使用。
- 不支持与WITH、USING、ORDER BY、FROM组合使用。
- CURSOR对应的SELECT语句必须声明为FOR UPDATE。
- CURSOR对应的SELECT语句仅支持单表，不支持LIMIT/OFFSET，不支持带有子查询、子链接。
- 存储过程中声明为FOR UPDATE的CURSOR，在COMMIT/ROLLBACK后，将无法再次使用。
- 若CURSOR指向的行已经不存在，在ORA兼容性模式下将报错指定的行不存在（仅UPDATE时报错，DELETE不报错），其他兼容模式下不报错。
- ORDER BY
关键字具体请参见SELECT章节介绍。
- LIMIT
关键字具体请参见SELECT章节介绍。
- RETURNING output_expression
在所有需要更新的行都被更新之后，UPDATE命令用于计算返回值的表达式。
取值范围：使用任何TABLE以及FROM中列出的表的字段。*表示返回所有字段。
- output_name
字段的返回名称。
示例
- 修改表中所有数据。
--创建tbl_test1表并插入数据。 gaussdb=# CREATE TABLE tbl_test1(id int, info varchar(10)); gaussdb=# INSERT INTO tbl_test1 VALUES (1, 'A'), (2, 'B'); --查询。 gaussdb=# SELECT * FROM tbl_test1; id | info ----+------ 1 | A 2 | B (2 rows) --修改tbl_test1表中所有数据的info列。 gaussdb=# UPDATE tbl_test1 SET info = 'aa'; --查询tbl_test1表。 gaussdb=# SELECT * FROM tbl_test1; id | info ----+------ 1 | aa 2 | aa (2 rows)
- 修改表中部分数据。
--修改tbl_test1表中id=2的数据。 gaussdb=# UPDATE tbl_test1 SET info = 'bb' WHERE id = 2; --查询tbl_test1表。 gaussdb=# SELECT * FROM tbl_test1; id | info ----+------ 1 | aa 2 | bb (2 rows)
- 修改数据，并返回修改后的数据。
--修改tbl_test1表中id=1的数据，并指定返回info列。 gaussdb=# UPDATE tbl_test1 SET info = 'ABC' WHERE id = 1 RETURNING info; info ------ ABC (1 row) UPDATE 1 --删除tbl_test1表。 gaussdb=# DROP TABLE tbl_test1;
- 使用子查询，在修改数据的同时，根据已有数据，插入新的数据。
--建表。 gaussdb=# CREATE TABLE test_grade ( sid int, --学号 name varchar(50), --姓名 score char, --成绩 examtime date, --考试时间 last_exam boolean --是否是最后一次考试 ); --插入数据。 gaussdb=# INSERT INTO test_grade VALUES (1,'Scott','A','2008-07-08',1),(2,'Ben','D','2008-07-08',1),(3,'Jack','D','2008-07-08',1); --查询。 gaussdb=# SELECT * FROM test_grade; sid | name | score | examtime | last_exam -----+-------+-------+------------+----------- 3 | Jack | D | 2008-07-08 | t 1 | Scott | A | 2008-07-08 | t 2 | Ben | D | 2008-07-08 | t (3 rows) --2008-08-25 Ben参加了补考,成绩为B，正常步骤需要先修改last_exam为否,然后插入2008-08-25这一天的成绩。 gaussdb=# WITH old_exa AS ( UPDATE test_grade SET last_exam = 0 WHERE sid = 2 AND examtime = '2008-07-08' RETURNING sid, name ) INSERT INTO test_grade VALUES ( ( SELECT sid FROM old_exa ), (SELECT name FROM old_exa), 'B', '2008-08-25', 1 ); --查询。 gaussdb=# SELECT * FROM test_grade; sid | name | score | examtime | last_exam -----+-------+-------+------------+----------- 3 | Jack | D | 2008-07-08 | t 1 | Scott | A | 2008-07-08 | t 2 | Ben | D | 2008-07-08 | f 2 | Ben | B | 2008-08-25 | t (4 rows) --删除。 gaussdb=# DROP TABLE test_grade;
- 更新视图或子查询
示例1：更新子查询
--创建SCHEMA gaussdb=# CREATE SCHEMA upd_subqry; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = 'upd_subqry'; SET --创建表并插入数据 gaussdb=# CREATE TABLE t1 (x1 int, y1 int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'x1' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE gaussdb=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2" CREATE TABLE gaussdb=# INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (5, 5); INSERT 0 4 gaussdb=# INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (5, 5); INSERT 0 4 --通过子查询更新t1 gaussdb=# UPDATE (SELECT * FROM t1) SET y1 = 13 where y1 = 3; UPDATE 1 gaussdb=# UPDATE (SELECT * FROM t1 WHERE y1 < 2) SET y1 = 12 WHERE y1 = 2; UPDATE 0 --插入子查询带READ ONLY gaussdb=# UPDATE (SELECT * FROM t1 WITH READ ONLY) SET y1 = 1 WHERE y1 = 11; ERROR: cannot perform a DML operation on a read-only subquery. --插入多表连接的子查询 gaussdb=# UPDATE (SELECT * FROM t1, t2 WHERE x1 = x2) SET y1 = 11 WHERE y2 = 1; UPDATE 1 --插入带CHECK OPTION的多表连接子查询，连接列x1, x2不可更新 gaussdb=# UPDATE (SELECT * FROM t1, t2 WHERE x1 = x2 WITH CHECK OPTION) SET y1 = 1 WHERE y2 = 1; UPDATE 1 gaussdb=# UPDATE (SELECT * FROM t1, t2 WHERE x1 = x2 WITH CHECK OPTION) SET x1 = 6 WHERE y2 = 5; ERROR: virtual column not allowed here --删除SCHEMA gaussdb=# RESET CURRENT_SCHEMA; RESET gaussdb=# DROP SCHEMA upd_subqry CASCADE; NOTICE: drop cascades to 2 other objects DETAIL: drop cascades to table upd_subqry.t1 drop cascades to table upd_subqry.t2 DROP SCHEMA
示例2： 更新视图
--创建SCHEMA gaussdb=# CREATE SCHEMA upd_view; CREATE SCHEMA gaussdb=# SET CURRENT_SCHEMA = 'upd_view'; SET --创建表并插入数据 gaussdb=# CREATE TABLE t1 (x1 int, y1 int); NOTICE: The 'DISTRIBUTE BY' clause is not specified. Using 'x1' as the distribution column by default. HINT: Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column. CREATE TABLE gaussdb=# CREATE TABLE t2 (x2 int PRIMARY KEY, y2 int); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t2_pkey" for table "t2" CREATE TABLE gaussdb=# INSERT INTO t1 VALUES (1, 1), (2, 2), (3, 3), (5, 5); INSERT 0 4 gaussdb=# INSERT INTO t2 VALUES (1, 1), (2, 2), (3, 3), (5, 5); INSERT 0 4 --创建单表视图 gaussdb=# CREATE VIEW v_upd1 AS SELECT * FROM t1; CREATE VIEW gaussdb=# CREATE VIEW v_upd_read AS SELECT * FROM t1 WITH READ ONLY; CREATE VIEW --通过视图对t1更新 gaussdb=# UPDATE v_upd1 SET y1 = 13 where y1 = 3; UPDATE 1 gaussdb=# UPDATE v_upd_read SET y1 = 1 WHERE y1 = 11; ERROR: cannot perform a DML operation on a read-only subquery. --创建多表视图 gaussdb=# CREATE VIEW vv_upd AS SELECT * FROM t1, t2 WHERE x1 = x2; CREATE VIEW gaussdb=# CREATE VIEW vv_upd_wco AS SELECT * FROM t1, t2 WHERE x1 = x2 WITH CHECK OPTION; CREATE VIEW --通过连接视图对t1更新 gaussdb=# UPDATE vv_upd SET y1 = 1 WHERE y2 = 1; UPDATE 1 gaussdb=# UPDATE vv_upd_wco SET x1 = 6 WHERE y2 = 5; ERROR: virtual column not allowed here --删除SCHEMA gaussdb=# RESET CURRENT_SCHEMA; RESET gaussdb=# DROP SCHEMA upd_view CASCADE; NOTICE: drop cascades to 6 other objects DETAIL: drop cascades to table upd_view.t1 drop cascades to table upd_view.t2 drop cascades to view upd_view.v_upd1 drop cascades to view upd_view.v_upd_read drop cascades to view upd_view.vv_upd drop cascades to view upd_view.vv_upd_wco DROP SCHEMA ERROR: virtual column not allowed here
父主题： U
