使用子查询结果集更新列值

功能描述

Vastbase支持形如UPDATE(SELECT...)SET...的SQL语法，此句式允许使用子查询来生成要更新的数据集，并更新符合子查询条件的列值。

注意事项

该功能仅在数据库兼容模式为Oracle时支持（即数据库初始化时指定DBCOMPATIBILITY='A'）。
此语法的使用存在以下限制：
- 子查询必须返回一个可更新的结果集，即子查询中的表必须是可更新的表。
- 当前子查询的查询对象仅支持隐式内连接，如：SELECT select_list FROM t1,t2 WHERE t1.id=t2.id，不支持显式inner join/left join及视图等其他对象。
在Vastbase之前的版本中，支持UPDATE (SELECT targetlist FROM tablelist WHERE clause) COLID SET target = expr;的用法（子查询后需跟随别名，并且子查询from的表需指定别名），当前仍可沿用之前的语法。

语法格式

UPDATE ( SELECT targetlist FROM tablelist WHERE clause ) SET target = expr ;

参数说明

targetlist

子查询SELECT语句的目标列表。
tablelist

子查询指定的表名，必须是一个可更新的表。
clause

子查询中WHERE条件的子句。

其中至少要有一个基表上的字段和非目标表的字段的等值条件，以便能够从关联条件中匹配出非目标表的唯一一条数据。这要求数据表的连接字段需要有唯一约束或者主键索引。如果唯一约束或者主键索引包含多个字段，则这些字段均需要在WHERE条件中出现。
target

要修改的字段名。
- SET语句指定的目标字段必须来自同一个表，该表被称为基表。
- 被更新的所有字段应存在于子查询的结果集中，且等号两边的目标引用来自于子查询的目标列表。
expr

赋给目标字段的值或表达式。

示例

1、创建测试表并插入数据。

create table employees (id int,name text,department text);
INSERT INTO employees (id, name, department)
VALUES (1, '张三', 'IT'),
       (2, '李四', 'IT'),
       (3, '王五', 'HR'),
       (4, '赵六', 'Finance');
create table it_department(id int primary key,name text,salary VARCHAR(255));
INSERT INTO it_department (id, name, salary)
VALUES (1, '张三', 5000),
       (2, '李四', 6000),
       (3, '王五', 4000),
       (4, '赵六', 5500);

2、测试表分为员工表employees和IT部门表it_department，查看原始表数据：

select * from employees;
select * from it_department;

返回结果如下：

 id | name | department
----+------+------------
  1 | 张三 | IT
  2 | 李四 | IT
  3 | 王五 | HR
  4 | 赵六 | Finance
(4 rows)

 id | name | salary
----+------+--------
  1 | 张三 | 5000
  2 | 李四 | 6000
  3 | 王五 | 4000
  4 | 赵六 | 5500
(4 rows)

3、使用UPDATE+子查询+set语法更新基表it_department，将全部IT部门员工的薪资（salary）更新为之前的1.2倍。

UPDATE (SELECT * from employees e,it_department d where e.id=d.id and e.department='IT') SET d.salary = d.salary * 1.2;

4、查询更新结果。

select e.id,e.name,e.department,d.salary from  employees e,it_department d where e.id=d.id;

返回结果如下，IT部门员工的薪资已更新为初始值的1.2倍，其余员工薪资不变：

 id | name | department | salary
----+------+------------+--------
  1 | 张三 | IT         | 6000
  2 | 李四 | IT         | 7200
  3 | 王五 | HR         | 4000
  4 | 赵六 | Finance    | 5500
(4 rows)

示例2： 对比展示是否为参数a_format_behavior_compat_options设置update_subselect_check_unique属性的用法区别。

设置参数a_format_behavior_compat_options的值为'update_subselect_check_unique'，表示更新前会对子查询中非目标表的唯一索引进行检查，从而保证基表更新时的数据来源中，非目标表匹配到的记录只有一条。

1、创建测试表并插入数据。

create table a(id int , col1 text,col11 text);
create table b(id int,col2 text,col22 text);
insert into a(id,col1,col11) values(1,'1','v1');
insert into a(id,col1,col11) values(1,'2','v2');
insert into a(id,col1,col11) values(1,'1','v222222');
insert into b(id,col2,col22) values(1,'11','v3');
insert into b(id,col2,col22) values(2,'21','v4');

2、分别查看两个测试表的原始数据。

select * from a;
select * from b;

返回结果如下：

 id | col1 |  col11
----+------+---------
  1 | 1    | v1
  1 | 2    | v2
  1 | 1    | v222222
(3 rows)

 id | col2 | col22
----+------+-------
  1 | 11   | v3
  2 | 21   | v4
(2 rows)

3、设置参数a_format_behavior_compat_options的值为'update_subselect_check_unique'。

set a_format_behavior_compat_options='update_subselect_check_unique';

4、使用UPDATE+子查询+set语法更新基表b。

update (select a.col11,b.col22 from a,b where a.id=b.id and a.col1='1') set b.col22=a.col11;

对于where子句的条件，非目标表中存在多条数据可与基表中的数据匹配。上述语句执行失败，报错信息如下：

ERROR: cannot modify a column which maps to a non-key-preserved table

5、设置a_format_behavior_compat_options的值为空。

set a_format_behavior_compat_options='';

6、再次执行步骤4的更新语句。

update (select a.col11,b.col22 from a,b where a.id=b.id and a.col1='1') set b.col22=a.col11;

执行成功，基表b中有一条数据被更新。查看基表b的数据：

select * from b;

返回结果如下：

对于基表b中id为1的这行数据，a表中存在两条记录可与其匹配，但只能有一种更新结果，这可能导致更新结果与预期不符。因此，当用户在使用UPDATE+子查询+SET语法时，如有检测唯一索引的需要，可通过update_subselect_check_unique选项进行控制。

 id | col2 | col22
----+------+-------
  2 | 21   | v4
  1 | 11   | v222222
(2 rows)

VastbaseG100

使用子查询结果集更新列值

功能描述

注意事项

语法格式

参数说明

示例

弹框头部