更新日期:2024年09月11日
语法格式:
UPDATE [database_name.]table_name
SET col_name1=value [, col_name2=value ...]
[WHERE where_definition]
注意:Hive 引擎表开启了属性TBLPROPERTIES(”transactional”=”true”)时方可执行
UPDATE 操作。
注意:对Hive 引擎表执行UPDATE 操作,影响行数均返回0,这是由于HIVE 引
擎本身的限制。
UPDATE 示例,首先创建表并插入数据:
gbase> CREATE TABLE t0(id int) CLUSTERED BY(id) INTO 2 BUCKETS STORED AS ORC
TBLPROPERTIES('transactional' = 'true') ENGINE = 'HIVE';
Query OK, 0 rows affected
gbase>
CREATE
TABLE
t1(id
int,name
varchar(50))
CLUSTERED
BY(name)
INTO
2
BUCKETS
STORED
AS ORC TBLPROPERTIES('transactional' = 'true') ENGINE = 'HIVE';
Query OK, 0 rows affected
gbase> CREATE TABLE t2(id int) ENGINE = 'HIVE';
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 t1 VALUES(1,'baker'),(2,'hunter'),(3,'carter'),(4,'smith');
Query OK, 4 rows affected
Records: 4 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:更新t1 表的数据。
gbase> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 760
| 4 | smith |
| 3 | carter |
| 1 | baker |
| 2 | hunter |
+------+--------+
4 rows in set
gbase> UPDATE t1 SET t1.id = t1.id+1 WHERE t1.id > 1;
Query OK
Rows matched: -1 Changed: -1 Warnings: 0
gbase> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
| 5 | smith |
| 4 | carter |
| 1 | baker |
| 3 | hunter |
+------+--------+
4 rows in set
示例2:使用IN 的多表查询更新。
gbase> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
| 5 | smith |
| 4 | carter |
| 1 | baker |
| 3 | hunter |
+------+--------+
4 rows in set
gbase> UPDATE t1 SET t1.id = 10 WHERE t1.id IN (SELECT t2.id FROM t2);
Query OK
Rows matched: -1 Changed: -1 Warnings: 0
gbase> SELECT * FROM t1;
+------+--------+
| id | name |
+------+--------+
| 5 | smith |
| 10 | carter |
GBase UP 产品手册 5 数据库管理指南
文档版本04(2021-04-21) 南大通用数据技术股份有限公司 761
| 10 | baker |
| 3 | hunter |
+------+--------+
4 rows in set