How to move CTE for oracle update query in sqlalchemy

Question

I am facing the oracle error ORA-00928: missing SELECT keyword with a query, generated by sqlalchemy. The issue was already described and answered here.

My query looks like:

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)
)

and gets generated by this:

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

Now I am wondering how to tell sqlachemy to put the CTEs inside the WHERE clause and not above the 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)
)

Both of your update statements are making any sense to me. Why should you want to update all ids of your `table` to 1 if at least one row of `table` is present in `table2`? Is it what you're actually intending or is it is something simple like `UPDATE table SET id=1 where id in ( SELECT id FROM table3 )`? If this was what you intended, then there's no reason why it should generate a CTE at all. Moreover, why can't you simply run an update like that instead of relying on what sqlalchemy generates? — Kaushik Nayak, May 09 '19 at 15:07
thanks for your response. I just created a minimal example to make it more understandable. My key point is to move the WITH clause above the SELECT clause. — Hannes, May 09 '19 at 15:09
@Hannes Hopefully someone can provide an answer for how to change the way SQLAlchemy works, that would be the cleanest solution. I don't know anything about SQLAlchemy, but if you can't find any workaround and you have to work with the existing SQL, you might want to look into the [SQL Translation Framework](https://docs.oracle.com/database/121/DRDAA/sql_transl_arch.htm#DRDAA29132). That tool allows us to convert completely wrong queries into a correct syntax. It's meant for translating queries from SQL Server to Oracle, but it can also fix mistakes like this. — Jon Heller, May 10 '19 at 03:11

score 0 · Answer 1 · answered May 09 '19 at 19:44

CTE is a nice way to extract an inline view from the query and, by doing so, make your code easier to read and maintain.

When the CTE wasn't invented yet, we used inline views. Here are a few examples (based on Scott's schema) to show what I mean.

First, a 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

It can be moved into an inline 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

Or, using the old syntax, where tables (in the FROM clause) were separated by commas and joins were done within the WHERE clause (this might look familiar):

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

It means that your query might look like this; note the comment which shows position of your 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
                            )
               );

I think the OP knows how to use CTEs, his problem is that he doesn't know how to generate them correctly through a framework. — Jon Heller, May 10 '19 at 03:08
Aha. I've read your commend beneath the question, @Jon; I guess I understand what you are saying. On the other hand, *relocating* CTE's query and putting it into an inline view *might* help, if nothing else works. — Littlefoot, May 10 '19 at 05:02
Yes, in the end I had to go back to inline views. But thanks nonetheless — Hannes, May 13 '19 at 14:04

How to move CTE for oracle update query in sqlalchemy

1 Answers1