In Teradata 16.20 is there a way to update or merge from two CTEs?

Question

In Teradata 16.20 is there a way to update or merge from two CTEs?

For example, in MSSQL we have a first CTE, second CTE using the first CTE, then an update:

with CTE1 as (
 select alpha, beta
 from someTable a
 join otherTable b on a.aleph = b.aleph
), CTE2 as (
 select beta, gamma
 from CTE1 c
 join anotherTable d on c.alpha = d.alpha
)
update u
set u.gamma = e.gamma
from updateTable u
join CTE2 e on u.beta = e.beta;

In Teradata 16.20 this certainly works with one CTE, like this:

merge into mydb.mytable
using (
 select alpha, beta
 from someTable a
 join otherTable b on a.aleph = b.aleph
) as CTE (alpha, beta)
on mytable.alpha = CTE.alpha
when matched then update
set beta = CTE.beta;

Is there a way to do this with two or more CTEs?

Answer 1

You can't use WITH (CTE) inside a derived table (which is what you have in the USING clause of the MERGE statement above), but you can use nested derived tables:

merge into mydb.mytable u
using (
 select beta, gamma
 from (
   select alpha, beta
   from someTable a
   join otherTable b on a.aleph = b.aleph
 ) CTE1      
 join anotherTable d on CTE1.alpha = d.alpha
) CTE2 
on u.beta = CTE2.beta
when matched then update
set gamma = CTE2.gamma;

Or if MERGE is not applicable (e.g. join predicates don't include all the Primary Index columns) the same approach with joined UPDATE:

UPDATE u FROM mydb.mytable u, 
(
 select beta, gamma
 from (
   select alpha, beta
   from someTable a
   join otherTable b on a.aleph = b.aleph
 ) CTE1      
 join anotherTable d on CTE1.alpha = d.alpha
) CTE2 
set gamma = CTE2.gamma
WHERE u.beta = CTE2.beta;