Running Common Table expression in netezza as a script with Update at the end of CTE

Question

I am trying to run a CTE in script form

e.g

with CTE as 
(select * from sometable);

on running this code part alone in the I am getting the error

^ found "" (at char 152) expecting SELECT' or'('' */

Please let me know how to run this query. thanks

This question has nothing to do with *recusive CTEs*, and is therefore not an "exact duplicate" of http://stackoverflow.com/questions/29358821/convert-a-recursive-cte-in-sql-server-to-netezza — ScottMcG, Feb 17 '17 at 20:52

score 1 · Answer 1 · answered Feb 16 '17 at 20:24

1

You are getting this error because you aren't doing anything with the CTE you have defined, but the syntax requires something.

Add a SELECT after the CTE clause in the parentheses and it should work fine.

with CTE as 
(select * from sometable)
select * from CTE;

answered Feb 16 '17 at 20:24

ScottMcG

I agree but instead of the Select I need an update but when I do an update I am still getting error. So please let me know how can i update the CTE after creating it. thanks – Feb 17 '17 at 20:21
Can you update your question to include an example of what you are trying to accomplish with the CTE and the UPDATE? I can post an example of using a CTE with an UPDATE statement, but it would be better to understand what you are trying to accomplish – ScottMcG Feb 17 '17 at 21:10

1 Answers1