python - 如何在 sqlalchemy 中移动 Oracle 更新查询的 CTE

Question

我在查询中遇到了 oracle 错误 ORA-00928:缺少 SELECT 关键字，由 sqlalchemy 生成。该问题已被描述并回答 here .

我的查询如下所示:

WITH table2 (id) AS (
    SELECT id
    FROM table3
)

UPDATE table SET id=1 
WHERE EXISTS (
    SELECT * 
    FROM table 
    WHERE id IN (SELECT id FROM table2)
)

并由此生成:

table2 = session.query(table3.id).cte()
update(table).where(exists().where(table.id.in_(table2))).values(id=1)

现在我想知道如何告诉 sqlachemy 将 CTE 放在 WHERE 子句中，而不是放在 UPDATE 之上。

UPDATE table SET id=1 
WHERE EXISTS (
    WITH table2 (id) AS (
        SELECT id
        FROM table3
    )

    SELECT * 
    FROM table 
    WHERE id IN (SELECT id FROM table2)
)

Answer 1

CTE 是一种从查询中提取内联 View 的好方法，通过这样做，可以使您的代码更易于阅读和维护。

当 CTE 尚未发明时，我们使用内联 View 。这里有几个例子(基于 Scott 的模式)来展示我的意思。

首先，CTE:

SQL> with tab as
  2    (select deptno from dept
  3     where deptno > 10
  4    )
  5  select e.deptno, count(*)
  6  from emp e join tab t on t.deptno = e.deptno
  7  group by e.deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5

它可以移动到内联 View 中:

SQL> select e.deptno, count(*)
  2  from emp e join (select deptno from dept
  3                   where deptno > 10
  4                  ) t on t.deptno = e.deptno
  5  group by e.deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5

或者，使用旧语法，其中表(在FROM子句中)用逗号分隔，并在WHERE内完成连接子句(这可能看起来很熟悉):

SQL> select e.deptno, count(*)
  2  from emp e,
  3      (select deptno from dept
  4       where deptno > 10
  5      ) t
  6  where t.deptno = e.deptno
  7  group by e.deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          5

<小时/>

这意味着您的查询可能如下所示；请注意显示您的 CTE 位置的注释:

update table set
  id = 1
  where exists (select *
                from table
                where id in (select id 
                             from 
                               (select id from table3)  --> this line is your CTE
                            )
               );