KES_UPDATE命令介绍
关键字： 人大金仓 KingbaseES update
1 用途及作用
UPDATE 更改满足条件的所有行中指定列的值。只有要被修改的列需要在 SET 子句中提及，没有被显式修改的列保持它们之前的值。有两种方法使用包含在数据库其他表中的信息来修改一个表：使用子查询或者在 FROM 子句中指定额外的表。这种技术只适合特定的环境。
可选的 RETURNING 子句导致 UPDATE 基于实际被更新的每一行计算并且返回值。任何使用该表的列以及 FROM 中提到的其他表的列的表达式都能被计算。计算时会使用该表的列的新（更新后）值。 RETURNING 列表的语法和 SELECT 的输出列表相同。
2 前置条件
必须拥有该表上的 UPDATE 特权，或者至少拥有要被更新的列上的该特权。如果任何一列的值需要被 expressions 或者 condition 读取，还必须拥有该列上的 SELECT 特权。
3 语法
[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE [ ONLY ] table_name [part_extension_clause] [ * ] [ [ AS ] alias ] | sub_query [alias]
SET {[{ column_name = { expression | DEFAULT } |
( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] ) |
( column_name [, ...] ) = ( sub-SELECT )
} [, ...]] | [ ROW = record]}
[ FROM from_list ]
[ WHERE condition | WHERE CURRENT OF cursor_name ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]
4 语法语义介绍
- with_query
WITH 子句允许用户指定一个或者更多个在 UPDATE 中可用其名称引用的子查询。与SELECT中的WITH子句相同。
- table_name
要更新的表的名称（可以是模式限定的）。如果在表名前指定了 ONLY ，则只会更新所提及表中的匹配行，否则任何从所提及表继承得到的表中的匹配行也会被更新（就是PGSQL里的DISTINCT）。可选地，在表名之后指定 * 可以显式地指示要把后代表也包括在内。
- part_extension_clause
支持通过partitionsubpartition关键字指定分区名或者子分区名，实现对分区和子分区直接进行update操作。其中PARTITION FOR(partition_key_value) 指定分区键值，PARTITION （partition）指定分区名; SUBPARTITION FOR(partition_key_value, subpartition_key_value) 指定子分区键值，SUBPARTITION （subpartition）指定子分区名，这里的分区名是指oralce语法创建分区表时指定的分区名，而不是分区对象在系统表sys_class中存储的对象名。
- alias
目标表的一个替代名称。在提供了一个别名时，它会完全隐藏表的真实名称。
- sub_query
当子查询作为UPDATE目标时，UPDATE的目标是子查询中的基表；
Update set列必须在子查询的投影列中，且必须是基表中的列,
子查询投影列中不包含聚集函数（avg，count，max，min，sum等），distinct等；
不支持GROUP BY, ORDER BY, MODEL, CONNECT BY, START WITH clause子句；
子查询不支持Union/Intersect/Except/Minus等集合查询。
- column_name
table_name 所指定的表的一列的名称, 可以用表名(可以被模式名限定)做限定。如果需要，该列名可以用一个子域名称(子域名需要和表名、模式名一同用小括号包围起来进行限定)或者数组下标限定。
- expression
要被赋值给该列的一个表达式。该表达式可以使用该表中这一列或者其他列的旧值。
- DEFAULT
将该列设置为它的默认值（如果没有为它指定默认值表达式，默认值将会为 NULL）。
- sub-SELECT
一个 SELECT 子查询，它产生和在它之前的圆括号中的列表中一样多的输出列。被执行时，该子查询必须得到不超过一行。如果它得到一行，其列值会被赋予给目标列。如果它得不到行，NULL值将被赋予给目标列。该子查询可以引用被更新表中当前行的旧值。
- from_list
表表达式的列表，允许来自其他表的列出现在 WHERE 条件和更新表达式中。这类似于可以在 SELECT 语句的 FROM 中指定的表列表。注意目标表不能出现在 from_list 中，除非你想做自连接（这种情况下它必须以别名出现在 from_list 中）。
- condition
一个返回 boolean 类型值的表达式。让这个表达式返回 true 的行将会被更新。
- cursor_name
要在 WHERE CURRENT OF 条件中使用的游标名。要被更新的是从这个游标中最近取出的行。该游标必须是一个在 UPDATE 目标表上的非分组查询。注意 WHERE CURRENT OF 不能和一个布尔条件一起指定。
- output_expression
在每一行被更新后，要被 UPDATE 命令计算并且返回的表达式。该表达式可以使用 table_name 指定的表或者 FROM 列出的表中的任何列名。写 * 可以返回所有列。
- output_name
用于一个被返回列的名称。
- record
record变量名，变量类型是自定义RECORD或%ROWTYPE。UPDATE语句更新行数据为record变量值。对于行数据的每列，record的成员必须与表对应列类型兼容。如果表列有NOT NULL约束，对应的record成员不能有NULL值。record成员个数必须与前面table的列数相同，且类型兼容。否则报错。
5 测试用例
DROP TABLE IF EXISTS employee;
DROP TABLE IF EXISTS employee_base;
DROP TABLE IF EXISTS tb_salary;
CREATE TABLE employee_base (e_id int primary key, e_name text, age int, address char(50), m_name text);
INSERT INTO employee_base VALUES(1, 'Paul', 32, 'California', NULL),(2, 'Allen', 25, 'Texas', 'Paul'), (3, 'Teddy', 23, 'Norway', 'Paul'), (4, 'Mark', 25, 'Rich-Mond', 'Allen'), (5, 'David',27, 'Texas', 'Teddy'), (6, 'Kim', 22, 'South-Hall', 'Allen'), (7, 'James', 24, 'Houston', 'Kim');
CREATE TABLE employee (salary real) INHERITS(employee_base);
INSERT INTO employee VALUES (1, 'Paul', 32, 'California', NULL, 20000.00), (2, 'Allen', 25, 'Texas', 'Paul', 15000.00), (3, 'Teddy', 23, 'Norway', 'Paul', 20000.00), (4, 'Mark', 25, 'Rich-Mond', 'Allen', 65000.00), (5, 'David',27, 'Texas', 'Teddy', 85000.00), (6, 'Kim', 22, 'South-Hall', 'Allen', 45000.00), (7, 'Aylan', 25, 'Hebei', 'Kim', 20000.00);
CREATE TABLE tb_salary (u_id int primary key, salary real);
INSERT INTO tb_salary VALUES(1, 20000.00), (2, 15000.00), (3, 20000.00), (4, 65000.00), (5, 85000.00), (6, 45000.00), (7, 20000.00);
1. 使用WITH子查询
WITH RECURSIVE employee_recursive(e_id, e_name, m_name) AS (
SELECT e_id, e_name, m_name
FROM employee_base
WHERE m_name = 'Kim'
UNION ALL
SELECT er.e_id + 1, e.e_name, e.m_name
FROM employee_recursive er, employee_base e
WHERE er.e_name = e.m_name
) UPDATE ONLY employee_base eb SET(e_name, age, address) = ('Aylan', 25, 'Hebei') WHERE e_id IN (SELECT e_id FROM employee_recursive);
#执行结果：
#e_id | e_name | age | address | m_name
# 1 | Paul | 32 | California |
# 2 | Allen | 25 | Texas | Paul
# 3 | Teddy | 23 | Norway | Paul
# 4 | Mark | 25 | Rich-Mond | Allen
# 5 | David | 27 | Texas | Teddy
# 6 | Kim | 22 | South-Hall | Allen
# 7 | Aylan | 25 | Hebei | Kim2.更新分区表
DROP TABLE IF EXISTS t1;
CREATE TABLE t1( c1 int, create_time date) PARTITION BY RANGE (create_time) SUBPARTITION BY LIST(c1)
(
PARTITION par_01 VALUES less than(to_date('2006-03-01', 'yyyy-mm-dd')) (SUBPARTITION supar_01 VALUES(1), SUBPARTITION supar_02 VALUES(2)),
PARTITION par_02 VALUES less than(to_date('2006-04-01', 'yyyy-mm-dd')) (SUBPARTITION supar_03 VALUES(3), SUBPARTITION supar_04 VALUES(4)
));
INSERT INTO t1 PARTITION (par_01) VALUES(1, '2006-02-01');
INSERT INTO t1 SUBPARTITION (supar_02) VALUES(2, '2006-02-02');
INSERT INTO t1 PARTITION FOR ('2006-03-02') VALUES(3, '2006-03-02');
INSERT INTO t1 SUBPARTITION (supar_04) VALUES(4, '2006-03-03');
--更新分区表
UPDATE t1 PARTITION (par_01) SET create_time = '2006-02-02' WHERE c1= 1;
# c1 | create_time
# 1 | 2006-02-02 00:00:00
# 2 | 2006-02-02 00:00:00
# 3 | 2006-03-02 00:00:00
# 4 | 2006-03-03 00:00:00
UPDATE t1 SUBPARTITION (supar_02) SET create_time = '2006-02-03';
#c1 | create_time
# 1 | 2006-02-02 00:00:00
# 2 | 2006-02-03 00:00:00
# 3 | 2006-03-02 00:00:00
# 4 | 2006-03-03 00:00:00
UPDATE t1 PARTITION FOR ('2006-03-02') SET create_time = '2006-03-03' WHERE c1= 3;
#c1 | create_time
# 1 | 2006-02-02 00:00:00
# 2 | 2006-02-03 00:00:00
# 3 | 2006-03-03 00:00:00
# 4 | 2006-03-03 00:00:00
UPDATE t1 SUBPARTITION FOR ('2006-03-04', 4) SET create_time = '2006-03-04';
#c1 | create_time
# 1 | 2006-02-02 00:00:00
# 2 | 2006-02-03 00:00:00
# 3 | 2006-03-03 00:00:00
# 4 | 2006-03-04 00:00:003.UPDATE table_name
UPDATE ONLY employee_base AS eb SET e_name = 'Curry' WHERE e_id =1;执行结果：
# e_id | e_name | age | address | m_name
# 2 | Allen | 25 | Texas | Paul
# 3 | Teddy | 23 | Norway | Paul
# 4 | Mark | 25 | Rich-Mond | Allen
# 5 | David | 27 | Texas | Teddy
# 6 | Kim | 22 | South-Hall | Allen
# 7 | Aylan | 25 | Hebei | Kim
# 1 | Curry | 32 | California |4. 从子查询更新(sub_query)
UPDATE (SELECT * FROM employee) e SET e.e_name = 'Curry' WHERE e_id = 1;#执行结果：
# e_id | e_name | age | address | m_name | salary
# 2 | Allen | 25 | Texas | Paul | 15000
# 3 | Teddy | 23 | Norway | Paul | 20000
# 4 | Mark | 25 | Rich-Mond | Allen | 65000
# 5 | David | 27 | Texas | Teddy | 85000
# 6 | Kim | 22 | South-Hall | Allen | 45000
# 7 | Aylan | 25 | Hebei | Kim | 20000
# 1 | Curry | 32 | California | | 200005.使用{ column_name = { expression | DEFAULT}
UPDATE employee AS e SET e.address = 'Beijing', e.salary = 30000.00 WHERE e.e_id = 2;#执行结果：
# e_id | e_name | age | address | m_name | salary
# 3 | Teddy | 23 | Norway | Paul | 20000
# 4 | Mark | 25 | Rich-Mond | Allen | 65000
# 5 | David | 27 | Texas | Teddy | 85000
# 6 | Kim | 22 | South-Hall | Allen | 45000
# 7 | Aylan | 25 | Hebei | Kim | 20000
# 1 | Curry | 32 | California | | 20000
# 2 | Allen | 25 | Beijing | Paul | 300006.使用( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT } [, ...] )
UPDATE employee AS e SET (e.address, e.salary) = ('Tianjin', 25000.00) WHERE e.e_id = 3;#执行结果：
# e_id | e_name | age | address | m_name | salary
# 4 | Mark | 25 | Rich-Mond | Allen | 65000
# 5 | David | 27 | Texas | Teddy | 85000
# 6 | Kim | 22 | South-Hall | Allen | 45000
# 7 | Aylan | 25 | Hebei | Kim | 20000
# 1 | Curry | 32 | California | | 20000
# 2 | Allen | 25 | Beijing | Paul | 30000
# 3 | Teddy | 23 | Tianjin | Paul | 250007.使用set ( column_name [, ...] ) = ( sub-SELECT )
DROP TABLE IF EXISTS emp1;
CREATE TABLE emp1 AS TABLE employee WITH NO DATA;
INSERT INTO emp1 VALUES (7, 'Aylan', 25, 'Tianjin', 'Kim', 20000.00);
UPDATE employee AS e SET (e.e_name, e.age, e.address, e.salary) = (SELECT e_name, age, address, salary FROM emp1 WHERE e_id = 7) WHERE e.e_id = 7; #执行结果：
#e_id | e_name | age | address | m_name | salary
# 4 | Mark | 25 | Rich-Mond | Allen | 65000
# 5 | David | 27 | Texas | Teddy | 85000
# 6 | Kim | 22 | South-Hall | Allen | 45000
# 1 | Curry | 32 | California | | 20000
# 2 | Allen | 25 | Beijing | Paul | 30000
# 3 | Teddy | 23 | Tianjin | Paul | 25000
# 7 | Aylan | 25 | Tianjin | Kim | 20000 8. 使用FROM子句
UPDATE employee AS e SET e.salary = e.salary + 500 FROM tb_salary WHERE tb_salary.salary = 85000.00 AND tb_salary.u_id = e.e_id;#执行结果：
#e_id | e_name | age | address | m_name | salary
# 4 | Mark | 25 | Rich-Mond | Allen | 65000
# 6 | Kim | 22 | South-Hall | Allen | 45000
# 1 | Curry | 32 | California | | 20000
# 2 | Allen | 25 | Beijing | Paul | 30000
# 3 | Teddy | 23 | Tianjin | Paul | 25000
# 7 | Aylan | 25 | Tianjin | Kim | 20000
# 5 | David | 27 | Texas | Teddy | 855009. UPDATE 游标
DROP materialized VIEW mv_t1;
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a int, b varchar(32));
INSERT INTO t1 VALUES(197,'hh'), (198,'hh'), (199,'kk');
BEGIN;
DECLARE c1 CURSOR FOR SELECT a FROM t1 LIMIT 1;
FETCH first FROM c1;
UPDATE t1 SET b = 'aa' WHERE current of c1;
COMMIT;#执行结果：
# a | b
#198 | hh
# 199| kk
# 197| aa10. 使用RETURNING子句
UPDATE employee AS e SET (e.age, e.salary) = (30, 30000.00) WHERE e.e_id = 4 RETURNING e.age+1 AS age1; #执行结果：
#age1
# 31
1912
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
