Hi All

I have an update statement in which i am using a CTE (Common table Expression), but when i try to run its giving me syntax error. is CTE not supported inside update statements?. My db is Db2 V9.5 on Aix

My query is like the below.

update db2.tablename set col1=123 , col2='P'

where col3 in
( with temp 1 as (select a.col1,b.col2 from tableA A, tableB B where .....some conditions)
,
temp2 as (select a.col1 from tableA where ...some different conditions here)

select col1 from temp1 where temp1.col1=temp2.col1 where ....
)


SQL0104N An unexpected token "as" was found following "ol3 IN ( with temp1".
Expected tokens may include: "JOIN". SQLSTATE=42601

can some one please tell me why i am getting this error?

"...with temp 1 as..."

is there a blanc between "temp" and "1" ? What happens if you omit it ?

actually there is no space in my query between temp and 1. it a typo while posting here. it's as temp1 in my original query.

such syntax is not supported, use normal joins instead in subquery

Sometimes, you can use nested table expression insted of common table expression.

For example:

where col3 in
(
select temp1.col1
from (select a.col1,b.col2 from tableA A, tableB B where .....some conditions) AS temp1,
(select a.col1 from tableA where ...some different conditions here) AS temp2
where temp1.col1=temp2.col1 ....
)

Or you could use a merge statement

DB2 Recursive Query, with update - comp.databases.ibm-db2 | Google Groups