UPDATE

功能描述

更新表中的数据。UPDATE修改满足条件的所有行中指定的字段值，WHERE子句声明条件，SET子句指定的字段会被修改，没有出现的字段则保持它们的原值。

注意事项

  • 本章节仅包含shark新增语法，原openGauss的UPDATE语法未作删除和修改。原openGauss的UPDATE语法请参考章节UPDATE。
  • 新增支持table_hint子句。

语法格式

1. 单表更新：
2. [ WITH [ RECURSIVE ] with_query [, ...] ]
3. UPDATE [/*+ plan_hint */] [ ONLY ] table_name [ partition_clause ] [ * ] [ [ AS ] alias ] [table_hint_clause]
4. SET {column_name = { expression | DEFAULT }
5. |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]
6. [ FROM from_list] [JOIN join_table ON join_condition]... ]
7. [ WHERE condition | WHERE CURRENT OF cursor_name ]
8. [ ORDER BY {expression [ [ ASC | DESC | USING operator ]
9. [ LIMIT { count } ]
10. [ RETURNING {*
11. | {output_expression [ [ AS ] output_name ]} [, ...] }];

13. 多表更新：
14. [ WITH [ RECURSIVE ] with_query [, ...] ]
15. UPDATE [/*+ plan_hint */] table_list
16. SET {column_name = { expression | DEFAULT }
17. |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]
18. [ FROM from_list] [ WHERE condition ];

20. where sub_query can be:
21. SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
22. { * | {expression [ [ AS ] output_name ]} [, ...] }
23. [ FROM from_item [, ...] ] [JOIN join_table ON join_condition]... ]
24. [ WHERE condition ]
25. [ GROUP BY grouping_element [, ...] ]
26. [ HAVING condition [, ...] ]
27. [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
28. [ LIMIT { [offset,] count | ALL } ]
  • 其中table_hint子句table_hint_clause为：

1. WITH ( <table_hint> [, ...] )

参数说明

  • JOIN

JOIN包含 INNER JOIN，LEFT JOIN，RIGHT JOIN，FULL JOIN，CROSS JOIN。 - WITH ( <table_hint> [, …] )

  • 不同于SELECT子句，针对单个hint，WITH可选，针对UPDATE子句，WITH必选，table_hint支持给出一个列表选项，列表通过逗号或者空格分隔，即WITH (hint1)、WITH (hint1, hint2, …)、WITH (hint1 hint2 …)均支持，(hint1)不支持。
  • 支持的hint包括NOLOCK、READUNCOMMITTED、UPDLOCK、REPEATABLEREAD、SERIALIZABLE、READCOMMITTED、TABLOCK、TABLOCKX、PAGLOCK、ROWLOCK、NOWAIT、READPAST、XLOCK、SNAPSHOT、NOEXPAND。
  • 当上述hint需要当做标识符，用于列名、变量名等，需要设置d_format_behavior_compat_options = ‘enable_table_hint_identifier’，该变量默认值d_format_behavior_compat_options = ‘’。
  • 所有的hint仅语法支持，无实际含义。
  • 针对hint，会打印相关NOTICE信息。

table_hint子句示例

1. create table t1(c1 int);

3. update t1 with (xlock) set c1 = 10 where c1 = 5;
4. NOTICE:  The xlock option is currently ignored

6. update t1 with (xlock, nowait) set c1 = 3 where c1 = 10;
7. NOTICE:  The xlock option is currently ignored
8. NOTICE:  The nowait option is currently ignored

10. update t1 as alias_t1 with (xlock) set c1 = 20 where c1 = 2;
11. NOTICE:  The xlock option is currently ignored

13. update t1 as alias_t1 with (xlock, nowait) set c1 = 20 where c1 = 2;
14. NOTICE:  The xlock option is currently ignored
15. NOTICE:  The nowait option is currently ignored

17. update t1 as alias_t1 with (xlock nowait) set c1 = 20 where c1 = 2;
18. NOTICE:  The xlock option is currently ignored
19. NOTICE:  The nowait option is currently ignored

相关链接

UPDATE