DB2 v8插入CTE [英] DB2 v8 insert with CTE

问题描述

我需要从DB2 V8中的CTE（公用表表达式）中进行选择，并将结果插入到表中。
v8的相关文档很难理解，但对于v9，有一个明确的例子（ http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic= /com.ibm.db29.doc.apsg/db2z_createcte.htm ）：

I need to select from a CTE (common table expression) in DB2 v8 and insert the result into a table. The relevant documentation for v8 is hard to understand at first glance, but for v9 there's a clear example (http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.apsg/db2z_createcte.htm):

INSERT INTO vital_mgr (mgrno)
WITH VITALDEPT (deptno, se_count)  AS
(
     SELECT deptno, count(*)
     FROM DSN8910.EMP
     WHERE job = 'senior engineer'
     GROUP BY deptno
)
SELECT    d.manager
FROM      DSN8910.DEPT d
         , VITALDEPT s
WHERE     d.deptno = s.deptno
          AND s.se_count  >  (
               SELECT  AVG(se_count)
               FROM    VITALDEPT
          );

它在v8中不起作用。
应该如何写入v8？

It does not work in v8 though. How should it be written in v8?

推荐答案

有一个简单的解决方法这里，允许您使用常规WITH的INSERT或UPDATE声明。此黑客将适用于V8或更高版本的INSERT，对于V9或更高版本的UPDATE。

There's a simple workaround here that allows you to use an INSERT or UPDATE using a conventional WITH statement. This hack will work for INSERT on V8 or greater, and for UPDATE on V9 or greater.

V8或更高版本还有其他方法，通常使用子选项，但由于复杂性，我发现它们不实用。

There are other methods for V8 or greater, typically using sub-selects, but I find them to be unpractical due to their complexity.

这篇关于DB2 v8插入CTE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！