UPDATE

功能描述

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

注意事项

本章节仅包含dolphin新增语法，原openGauss的UPDATE语法未作修改。原openGauss的UPDATE语法请参考章节UPDATE

语法格式

单表更新：

2. [ WITH [ RECURSIVE ] with_query [, ...] ]
3. UPDATE [/*+ plan_hint */] [IGNORE] [ ONLY ] table_name [ partition_clause ] [ * ] [ [ AS ] alias ]
4. SET {column_name = { expression | DEFAULT }
5. |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]
6. [ FROM from_list] [ WHERE condition ]
7. [ ORDER BY {expression [ [ ASC | DESC | USING operator ]
8. [ LIMIT { count } ]
9. [ RETURNING {*
10. | {output_expression [ [ AS ] output_name ]} [, ...] }];

多表更新：

1. [ WITH [ RECURSIVE ] with_query [, ...] ]
2. UPDATE [/*+ plan_hint */] [IGNORE] table_list
3. SET {column_name = { expression | DEFAULT }
4. |( column_name [, ...] ) = {( { expression | DEFAULT } [, ...] ) |sub_query }}[, ...]
5. [ FROM from_list] [ WHERE condition ];

7. where sub_query can be:

9. SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] ]
10. { * | {expression [ [ AS ] output_name ]} [, ...] }
11. [ FROM from_item [, ...] ]
12. [ WHERE condition ]
13. [ GROUP BY grouping_element [, ...] ]
14. [ HAVING condition [, ...] ]
15. [ ORDER BY {expression [ [ ASC | DESC | USING operator ] | nlssort_expression_clause ] [ NULLS { FIRST | LAST } ]} [, ...] ]
16. [ LIMIT { [offset,] count | ALL } ]

参数说明

  • IGNORE

若带有IGNORE关键字的UPDATE语句执行时在指定场景引发了Error，则会将Error降级为Warning，且继续语句的执行，不会影响其他数据的操作。能使Error降级的场景有：

1.违反非空约束时：

若执行的SQL语句违反了表的非空约束，使用此hint可将Error降级为Warning，并根据GUC参数sql_ignore_strategy的值采用以下策略的一种继续执行： - sql_ignore_startegy为ignore_null时，忽略违反非空约束的行的UPDATE操作，并继续执行剩余数据操作。 - sql_ignore_startegy为overwrite_null时，将违反约束的null值覆写为目标类型的默认值，并继续执行剩余数据操作。

UPDATE - 图1 说明：

GUC参数sql_ignore_strategy为枚举类型，可选值有：ignore_null, overwrite_null

2.违反唯一约束时：

若执行的SQL语句违反了表的唯一约束，使用此hint可将Error降级为Warning，忽略违反约束的行的UPDATE操作，并继续执行剩余数据操作。

3.分区表无法匹配到合法分区时

在对分区表进行UPDATE操作时，若某行数据无法匹配到表格的合法分区，使用此hint可将Error降级为Warning，忽略该行操作，并继续执行剩余数据操作。

4.更新值向目标列类型转换失败时：

执行UPDATE语句时，若发现新值与目标列类型不匹配，使用此hint可将Error降级为Warning，并根据新值与目标列的具体类型采取以下策略的一种继续执行：

1. -   当新值类型与列类型同为数值类型时：

3. 若新值在列类型的范围内，则直接进行更新；若新值在列类型范围外，则以列类型的最大/最小值替代。

5. -   当新值类型与列类型同为字符串类型时：

7. 若新值长度在列类型限定范围内，则以直接进行更新；若新值长度在列类型的限定范围外，则保留列类型长度限制的前n个字符。

9. -   若遇到新值类型与列类型不可转换时：

11. 更新为列类型的默认值。

IGNORE关键字不支持列存，无法在列存表中生效。

示例

IGNORE关键字

为使用ignore_error hint，需要创建B兼容模式的数据库，名称为db_ignore。

1. create database db_ignore dbcompatibility 'B';
2. \c db_ignore
  • 忽略非空约束
1. db_ignore=# create table t_not_null(num int not null);
2. CREATE TABLE

4. -- 采用忽略策略

6. db_ignore=# set sql_ignore_strategy = 'ignore_null';
7. SET
8. db_ignore=# insert into t_not_null values (1);
9. INSERT 0 1
10. db_ignore=# select * from t_not_null ;
11. num
12. -----
13. 1
14. (1 row)

16. db_ignore=# update /*+ ignore_error */ t_not_null set num = null where num = 1;
17. WARNING:  null value in column "num" violates not-null constraint
18. DETAIL:  Failing row contains (null).
19. UPDATE 0
20. db_ignore=# select * from t_not_null ;
21. num
22. -----
23. 1
24. (1 row)

27. -- 采用覆写策略

29. db_ignore=# delete from t_not_null;
30. db_ignore=# set sql_ignore_strategy = 'overwrite_null';
31. SET
32. db_ignore=# insert into t_not_null values (1);
33. WARNING:  null value in column "num" violates not-null constraint
34. DETAIL:  Failing row contains (null).
35. INSERT 0 1
36. db_ignore=# select * from t_not_null ;
37. num
38. -----
39. 1
40. (1 rows)

42. db_ignore=# update /*+ ignore_error */ t_not_null set num = null where num = 1;
43. WARNING:  null value in column "num" violates not-null constraint
44. DETAIL:  Failing row contains (null).
45. UPDATE 1
46. db_ignore=# select * from t_not_null ;
47. num
48. -----
49. 0
50. (1 rows)
  • 忽略唯一约束
1. db_ignore=# create table t_unique(num int unique);
2. NOTICE:  CREATE TABLE / UNIQUE will create implicit index "t_unique_num_key" for table "t_unique"
3. CREATE TABLE
4. db_ignore=# insert into t_unique values(1), (2);
5. INSERT 0 1
6. db_ignore=# update /*+ ignore_error */ t_unique set num = 1 where num = 2;
7. WARNING:  duplicate key value violates unique constraint in table "t_unique"
8. UPDATE 0
9. db_ignore=# select * from t_unique ;
10. num
11. -----
12. 1
13. 2
14. (2 rows)
  • 忽略分区表无法匹配到合法分区
1. db_ignore=# CREATE TABLE t_ignore
2. db_ignore-# (
3. db_ignore(#     col1 integer NOT NULL,
4. db_ignore(#     col2 character varying(60)
5. db_ignore(# ) WITH(segment = on) PARTITION BY RANGE (col1)
6. db_ignore-# (
7. db_ignore(#     PARTITION P1 VALUES LESS THAN(5000),
8. db_ignore(#     PARTITION P2 VALUES LESS THAN(10000),
9. db_ignore(#     PARTITION P3 VALUES LESS THAN(15000)
10. db_ignore(# );
11. CREATE TABLE
12. db_ignore=# insert into t_ignore values(3000);
13. INSERT 0 1
14. db_ignore=# select * from t_ignore ;
15. col1 | col2
16. ------+------
17. 3000 |
18. (1 row)
19. db_ignore=# update /*+ ignore_error */ t_ignore set col1 = 20000 where col1 = 3000;
20. WARNING:  fail to update partitioned table "t_ignore".new tuple does not map to any table partition.
21. UPDATE 0
22. db_ignore=# select * from t_ignore ;
23. col1 | col2
24. ------+------
25. 3000 |
26. (1 row)
  • 更新值向目标列类型转换失败
1. -- 当新值类型与列类型同为数值类型

3. db_ignore=# create table t_tinyint(num tinyint);
4. CREATE TABLE
5. db_ignore=# insert into t_tinyint values(1);
6. INSERT 0 1
7. db_ignore=# select * from t_tinyint;
8. num
9. -----
10. 1
11. (1 row)
12. db_ignore=# update /*+ignore_error */ t_tinyint set num = 10000 where num = 1;
13. WARNING:  tinyint out of range
14. CONTEXT:  referenced column: num
15. UPDATE 1
16. db_ignore=# select * from t_tinyint;
17. num
18. -----
19. 127
20. (1 row)

23. -- 当新值类型与列类型同为字符类型时

25. db_ignore=# create table t_varchar5(content varchar(5));
26. CREATE TABLE
27. db_ignore=# insert into t_varchar5 values('abc');
28. INSERT 0 1
29. db_ignore=# select * from t_varchar5;
30. content
31. ---------
32. abc
33. (1 row)
34. db_ignore=# update /*+ ignore_error */ t_varchar5 set content = 'abcdefghijklmn' where content = 'abc';
35. WARNING:  value too long for type character varying(5)
36. CONTEXT:  referenced column: content
37. UPDATE 1
38. db_ignore=# select * from t_varchar5;
39. content
40. ---------
41. abcde
42. (1 row)