How to update Column of one CTE with another CTE in oracle-CodePudding

I Have two CTE let's say A and B, and I want to update a column of A with CTE B.

WITH cte_A AS ( SELECT X, 0 AS Y from table_1 -- Some complex logic for Y that is why updating with other CTE )

UPDATE cte_A  SET Y = (
WITH CTE_B AS (SELECT Y FROM table_2 )
SELECT Y FROM CTE_B WHERE CTE_B.ID =  cte_A.ID
)

SELECT * FROM cte_A

I am getting errors like missing SELECT keyword in oracle

CodePudding user response：

You cannot UPDATE a query; you UPDATE a table (or a view). However, you don't need to update it, you just need to display data from the two sources.

What you can do is use JOIN the two tables and instead of selecting * from CTE_A select Y from CTE_B and the other columns from CTE_A (and given your logic you appear to be using an OUTER JOIN):

WITH cte_A (id, x, y) AS (
  SELECT id, x, 0 from table_1
),
CTE_B (id, y) AS (
  SELECT id, y FROM table_2
)
SELECT a.id,
       a.x,
       b.y
FROM   CTE_A a
       LEFT OUTER JOIN CTE_B b
       ON b.ID =  a.ID

CodePudding user response：

You can't; a CTE is just another way of writing a subquery. You can't update a subquery; you update tables (in most cases), rarely views, but subqueries - nope.

In your case, you'd actually update table_1. merge might be a good choice:

MERGE INTO table_1 a
     USING table_2 b
        ON (a.id = b.id)
WHEN MATCHED
THEN
   UPDATE SET a.y = b.y;

OK, I understand that there's some "complex logic" involved so the query might need to be modified, but - that's the general idea.