How to use CTE as source for merge?

Database

How to use CTE as source for merge?

I'm trying to make use of a CTE as the source of a merge statement but continually get parse errors. I have independently verified that I can select from the CTE and that the merge statement works when using a standard table as the source.

 

I can use a view as the source or a volatile table, but the CTE doesn't want to work. If I simply put the expression inside the CTE inside  the source declaration it works. This is my workaround but I personally find the code more readable when it uses a CTE.

 

What my current solution looks like

MERGE destTable as t

USING (Select column1, column2 from table1) as s

 

 

What I'd like it to look like but can't make work.

 

WITH cte as( Select column1, column2 from table1)

 

MERGE destTable as t

USING cte as s


Accepted Solutions
Teradata Employee

Re: How to use CTE as source for merge?

In the section on "WITH Modifier" in the SQL DML manual, the 3rd paragraph says, "Note: You can only use the WITH modifier with SELECT statements. You cannot use the WITH modifier with other DML statements."

As John Cleese famously said in A Fish Called Wanda, "That's it, then."  This would be a great idea for an enhancement request, which you can create in a customer incident.

1 ACCEPTED SOLUTION
2 REPLIES
Teradata Employee

Re: How to use CTE as source for merge?

In the section on "WITH Modifier" in the SQL DML manual, the 3rd paragraph says, "Note: You can only use the WITH modifier with SELECT statements. You cannot use the WITH modifier with other DML statements."

As John Cleese famously said in A Fish Called Wanda, "That's it, then."  This would be a great idea for an enhancement request, which you can create in a customer incident.

Re: How to use CTE as source for merge?

I figured this was likely the case. Thank you for the response though! I looked through the documentation and was unable to find that definitive answer.

 

Thanks again.