数据仓库服务 GAUSSDB(DWS)-WITH表达式:WITH中的数据修改语句

时间:2024-04-12 10:20:16

WITH中的数据修改语句

在WITH子句中使用数据修改命令INSERT、UPDATE、DELETE。这允许用户在同一个查询中执行多个不同操作。示例如下所示:

1
2
3
4
5
6
WITH moved_tree AS (
     DELETE FROM tree
     WHERE parentid = 4 
     RETURNING * )
 INSERT INTO tree_log
 SELECT * FROM moved_tree;

上述查询示例实际上从tree把行移动到tree_log。WITH中的DELETE删除来自tree的指定行,以它的RETURNING子句返回它们的内容,并且接着主查询读该输出并将它插入到tree_log。

WIYH子句中的数据修改语句必须有RETURNING子句,用来返回RETURNING子句的输出,而不是数据修改语句的目标表,RETURNING子句形成了可以被查询的其余部分引用的临时表。如果一个WITH中的数据修改语句缺少一个RETURNING子句,则它形不成临时表并且不能在剩余的查询中被引用。

如果声明了RECURSIVE关键字,则不允许在数据修改语句中进行递归自引用。在某些情况中可以通过引用递归WITH的输出来绕过这个限制,例如:

1
2
3
4
5
6
7
8
9
WITH RECURSIVE included_parts(sub_part, part) AS (
     SELECT sub_part, part FROM parts WHERE part = 'our_product'
   UNION ALL
     SELECT p.sub_part, p.part
     FROM included_parts pr, parts p
     WHERE p.part = pr.sub_part
   ) 
DELETE FROM parts
   WHERE part IN (SELECT part FROM included_parts);

这个查询将会移除一个产品的所有直接或间接子部件。

WITH子句中的子语句与主查询同时执行。因此,在使用WITH中的数据修改语句时,指定更新的顺序实际是以不可预测的方式发生的。所有的语句都使用同一个快照中执行,语句的效果在目标表上不可见。这减轻了行更新的实际顺序的不可预见性的影响,并且意味着RETURNING数据是在不同WITH子语句和主查询之间传达改变的唯一方法。

本示例中外层SELECT可以返回更新之前的数据:

1
2
3
4
WITH t AS (
     UPDATE tree SET id = id + 1
     RETURNING * ) 
SELECT * FROM tree;

本示例中外部SELECT将返回更新过的数据:

1
2
3
4
WITH t AS (     
UPDATE tree SET id = id + 1
     RETURNING * )
SELECT * FROM t;

不支持在单个语句中更新同一行两次。这种语句的效果是不可预测的。如果只有一个修改发生了,但却不容易(有时也不可能)预测哪一个发生了修改。

support.huaweicloud.com/devg-820-dws/dws_04_1002.html