语法格式
UPDATE [database_name.]table_name
[PARTITION (partition_name)]
SET col_name1=expr1 [, col_name2=expr2 ...]
[WHERE where_definition]
说明
当更新列的值是一个合法的表达式时，也可以进行正确的更新赋值操作。
需要说明的是，
UPDATE 操作不支持更新DISTRIBUTED BY 列和分区表中RANGE
列的值。
PARTITION 只能用于跨引擎分区表，更新分区表中分区名为 partition_name 中的
数据，其他分区中符合要求的数据不更新。
对于跨引擎分区表，UPDATE 只能更新当前最新分区的数据，如果UPDATE 语句
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 693
的WHERE 条件命中了除最新分区以外的其他分区，或者partition_name 中有最新
分区以外的其他分区，语句将会报错。
示例
首先创建表t0 和t2，并插入数据。
gbase> CREATE TABLE t0(id int) REPLICATED;
Query OK, 0 rows affected
gbase> CREATE TABLE t2(id int);
Query OK, 0 rows affected
gbase> INSERT INTO t0(id) VALUES(1),(2),(3),(4),(5),(6),(2),(3),(1);
Query OK, 9 rows affected
Records: 9 Duplicates: 0 Warnings: 0
gbase> INSERT INTO t2(id) VALUES(1),(2),(4);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
示例1：更新t0 表的数据。
gbase> SELECT * FROM t0;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 2 |
| 3 |
| 1 |
+------+
9 rows in set
gbase> UPDATE t0 SET t0.id = t0.id+1 WHERE t0.id > 1;
Query OK, 7 rows affected
Rows matched: 7 Changed: 7 Warnings: 0
gbase> SELECT * FROM t0;
+------+
| id |
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 694
+------+
| 1 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 3 |
| 4 |
| 1 |
+------+
9 rows in set
示例2：使用IN 的多表查询更新。
gbase> SELECT * FROM t0;
+------+
| id |
+------+
| 2 |
| 3 |
| 5 |
| 5 |
| 6 |
| 7 |
| 3 |
| 5 |
| 2 |
+------+
9 rows in set
gbase> UPDATE t0 SET t0.id = 10 WHERE t0.id IN (SELECT t2.id FROM t2);
Query OK, 2 rows affected
Rows matched: 2 Changed: 2 Warnings: 0
gbase> SELECT * FROM t0;
+------+
| id |
+------+
| 10 |
| 3 |
| 5 |
| 5 |
| 6 |
| 7 |
| 3 |
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 695
| 5 |
| 10 |
+------+
9 rows in set
示例3：子查询中包含更新列，更新成功。
gbase> CREATE TABLE t0(id int) REPLICATED;
Query OK, 0 rows affected
gbase> INSERT INTO t0(id) VALUES(1),(2),(3),(4),(5),(6),(2),(3),(1);
Query OK, 9 rows affected
Records: 9 Duplicates: 0 Warnings: 0
gbase> SELECT * FROM t0;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 2 |
| 3 |
| 1 |
+------+
9 rows in set
gbase> UPDATE t0 SET t0.id = (SELECT id FROM t0 WHERE id = 6);
Query OK, 9 rows affected
Rows matched: 9 Changed: 9 Warnings: 0
示例4：不允许更新DISTRIBUTED BY 列的值。
gbase> CREATE TABLE student (stu_no int, stu_name varchar(200),stu_sex int)
DISTRIBUTED BY('stu_no');
Query OK, 0 rows affected
gbase>
INSERT
INTO
student
(stu_no,stu_name,stu_sex)
VALUES(1,'Tom',0),(2,'Jim',0),(3,'Rose',1);
Query OK, 3 rows affected
Records: 3 Duplicates: 0 Warnings: 0
gbase> SELECT * FROM student;
+--------+----------+---------+
| stu_no | stu_name | stu_sex |
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 696
+--------+----------+---------+
| 1 | Tom | 0 |
| 2 | Jim | 0 |
| 3 | Rose | 1 |
+--------+----------+---------+
3 rows in set
gbase> UPDATE student SET stu_no = 4 WHERE stu_no = 2;
ERROR 1722 (HY000): (GBA-02DD-0006) Can't update distributed column 'stu_no'
示例5：UPDATE 时，更新数据行中的TIMESTAMP 列的数值自动更新。
示例中用到的表及数据如下：
DROP TABLE IF EXISTS t2;
CREATE TABLE t2(a timestamp,b decimal(30,4),c int) DISTRIBUTED BY ('c');
INSERT INTO t2(b,c) VALUES(534.536,1);
INSERT INTO t2(b,c) VALUES(3534.56,11);
INSERT INTO t2(b,c) VALUES(33534.576,111);
INSERT INTO t2(b,c) VALUES(1334.56,1111);
INSERT INTO t2(b,c) VALUES(334.565,11111);
INSERT INTO t2(b,c) VALUES(34.5,111111);
INSERT INTO t2(b,c) VALUES(35.56,10009);
INSERT INTO t2(b,c) VALUES(3222.56,1897);
INSERT INTO t2(b,c) VALUES(3255.56,123);
INSERT INTO t2(b,c) VALUES(325.56,2);
查看t2 表的原始数据：
gbase> SELECT * FROM t2 ORDER BY a;
+---------------------+------------+--------+
| a | b | c |
+---------------------+------------+--------+
| 2013-12-17 14:11:16 | 3534.5600 | 11 |
| 2013-12-17 14:11:16 | 33534.5760 | 111 |
| 2013-12-17 14:11:16 | 1334.5600 | 1111 |
| 2013-12-17 14:11:16 | 334.5650 | 11111 |
| 2013-12-17 14:11:16 | 3222.5600 | 1897 |
| 2013-12-17 14:11:16 | 3255.5600 | 123 |
| 2013-12-17 14:11:16 | 534.5360 | 1 |
| 2013-12-17 14:11:16 | 34.5000 | 111111 |
| 2013-12-17 14:11:16 | 35.5600 | 10009 |
| 2013-12-17 14:11:16 | 325.5600 | 2 |
+---------------------+------------+--------+
10 rows in set
更新数据：
gbase> UPDATE t2 SET b=89.3 where c <1000;
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 697
Query OK, 5 rows affected
Rows matched: 5 Changed: 5 Warnings: 0
查看更新行对应的TIMESTAMP 列，TIMESTAMP 列的值自动被更新。
gbase> SELECT * FROM t2 WHERE c <1000;
+---------------------+---------+------+
| a | b | c |
+---------------------+---------+------+
| 2013-12-17 14:17:57 | 89.3000 | 11 |
| 2013-12-17 14:17:57 | 89.3000 | 111 |
| 2013-12-17 14:17:57 | 89.3000 | 123 |
| 2013-12-17 14:17:57 | 89.3000 | 1 |
| 2013-12-17 14:17:57 | 89.3000 | 2 |
+---------------------+---------+------+
5 rows in set
查看没有被更新的数据行对应的TIMESTAMP 列，
TIMESTAMP 列的值保持不
变。
gbase> SELECT * FROM t2 WHERE c >=1000;
+---------------------+-----------+--------+
| a | b | c |
+---------------------+-----------+--------+
| 2013-12-17 14:11:16 | 1334.5600 | 1111 |
| 2013-12-17 14:11:16 | 334.5650 | 11111 |
| 2013-12-17 14:11:16 | 3222.5600 | 1897 |
| 2013-12-17 14:11:16 | 34.5000 | 111111 |
| 2013-12-17 14:11:16 | 35.5600 | 10009 |
+---------------------+-----------+--------+
5 rows in set
示例6：更新指定分区的数据。
示例中用到的表及数据如下：
gbase> show create table t1;
| t1 | CREATE TABLE "t1" (
"a" int(11) DEFAULT NULL,
"b" varchar(100) DEFAULT NULL,
"c" date DEFAULT NULL
) ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'
PARTITION BY RANGE (c) 'dlm_cur_date=20160621,dlm_create_date=20160618'
(PARTITION p1 VALUES LESS THAN ( date_sub(curdate(), interval 1 week)) TABLESPACE
= 'sys_tablespace' ENGINE = HIVE,
PARTITION p2 VALUES LESS THAN MAXVALUE TABLESPACE = 'sys_tablespace' ENGINE =
EXPRESS) |
1 row in set (Elapsed: 00:00:00.00)
gbase> select * from t1 order by c;
+------+-----------+------------+
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 698
| a | b | c |
+------+-----------+------------+
| 1 | black | 2016-01-01 |
| 2 | red | 2016-01-02 |
| 3 | blue | 2016-01-03 |
| 4 | yellow | 2016-01-04 |
| 5 | green | 2016-01-05 |
| 6 | white | 2016-01-06 |
| 7 | gray | 2016-01-07 |
| 8 | desk | 2016-01-08 |
| 9 | stone | 2016-01-09 |
| 10 | safe | 2016-01-10 |
| 11 | one | 2016-01-11 |
| 12 | two | 2016-01-12 |
| 13 | three | 2016-01-13 |
| 14 | four | 2016-01-14 |
| 15 | five | 2016-01-15 |
| 16 | six | 2016-01-16 |
| 300 | tianjin | 2016-06-11 |
| 400 | chongqing | 2016-06-12 |
| 500 | guangzhou | 2016-06-13 |
| 600 | shenzhen | 2016-06-14 |
| 700 | nanjing | 2016-06-15 |
| 800 | xiamen | 2016-06-16 |
| 900 | wuhan | 2016-06-17 |
| 1000 | chengdu | 2016-06-18 |
+------+-----------+------------+
24 rows in set (Elapsed: 00:00:12.04)
gbase> select * from t1 partition (p2) order by c;
+------+----------+------------+
| a | b | c |
+------+----------+------------+
| 600 | shenzhen | 2016-06-14 |
| 700 | nanjing | 2016-06-15 |
| 800 | xiamen | 2016-06-16 |
| 900 | wuhan | 2016-06-17 |
| 1000 | chengdu | 2016-06-18 |
+------+----------+------------+
5 rows in set (Elapsed: 00:00:00.08)
-- 更新 p3 分区中的数据
gbase> update t1 partition (p2) set a = a + 1;
Query OK, 5 rows affected (Elapsed: 00:00:00.66)
Rows matched: 5 Changed: 5 Warnings: 0
-- p3 分区中的数据已经更新
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 699
gbase> select * from t1 partition (p2) order by c;
+------+----------+------------+
| a | b | c |
+------+----------+------------+
| 601 | shenzhen | 2016-06-14 |
| 701 | nanjing | 2016-06-15 |
| 801 | xiamen | 2016-06-16 |
| 901 | wuhan | 2016-06-17 |
| 1001 | chengdu | 2016-06-18 |
+------+----------+------------+
5 rows in set (Elapsed: 00:00:00.08)
-- 其他分区中的数据没有更新
gbase> select * from t1 order by c;
+------+-----------+------------+
| a | b | c |
+------+-----------+------------+
| 1 | black | 2016-01-01 |
| 2 | red | 2016-01-02 |
| 3 | blue | 2016-01-03 |
| 4 | yellow | 2016-01-04 |
| 5 | green | 2016-01-05 |
| 6 | white | 2016-01-06 |
| 7 | gray | 2016-01-07 |
| 8 | desk | 2016-01-08 |
| 9 | stone | 2016-01-09 |
| 10 | safe | 2016-01-10 |
| 11 | one | 2016-01-11 |
| 12 | two | 2016-01-12 |
| 13 | three | 2016-01-13 |
| 14 | four | 2016-01-14 |
| 15 | five | 2016-01-15 |
| 16 | six | 2016-01-16 |
| 300 | tianjin | 2016-06-11 |
| 400 | chongqing | 2016-06-12 |
| 500 | guangzhou | 2016-06-13 |
| 601 | shenzhen | 2016-06-14 |
| 701 | nanjing | 2016-06-15 |
| 801 | xiamen | 2016-06-16 |
| 901 | wuhan | 2016-06-17 |
| 1001 | chengdu | 2016-06-18 |
+------+-----------+------------+
24 rows in set (Elapsed: 00:00:13.36)
GBase UP 产品手册 5 数据库管理指南
文档版本04（2021-04-21） 南大通用数据技术股份有限公司 700