Update on CTE

MC97
New Contributor

‎10-30-2024 02:13 PM

So 

I am reflecting a business logic from on prem to azure databricks . 
what on prem did is 

created the table and after that updated . 
I have to construct that as a single query . 
Example 

Create or replace table table1

with CTE 1 as (

) ,

CTE 2 as (

select some columns from cte1

) , 

CTE 3 as (

select some columns from CTE2

)

select * from CTE3 

 

but there are some updates from on prem which I need to apply to CTE2 . 
can I apply those updates after CTE2 before CTE 3 ? 

 

 

 

0 Kudos

VZLA
Databricks Employee
Databricks Employee

‎10-31-2024 04:27 AM

An actual "Update", it may not be possible, but have you consider and will something like this work for you? This is simulating updates within the query without actual UPDATE statements:

CREATE OR REPLACE TABLE table1 AS
WITH CTE1 AS (
    -- Your initial query for CTE1
),
CTE2 AS (
    SELECT 
        CASE WHEN condition THEN updated_value ELSE original_value END AS column_name,
        ...
    FROM CTE1
),
CTE3 AS (
    SELECT *
    FROM CTE2
)
SELECT *
FROM CTE3;

Feel free to clarify if there's a misunderstanding about the requirement. Meanwhile, hope it helps.

0 Kudos